如何通过循环批量将Sheet1项目评级转换为Sheet3数值（按钮触发）

Tired of those clunky nested IF formulas? Let's build a quick VBA script that lets you click a button to convert all your ratings (low/medium/high/extreme) from Sheet1 to numeric values in Sheet3 automatically. Here's exactly how to do it:

Step 1: Open the VBA Editor & Insert a Module #

• Open your Excel file, press Alt + F11 to launch the VBA Editor.
• Right-click your workbook name in the Project Explorer (left pane) > Insert > Module.

Step 2: Paste the VBA Code #

Copy this code into the module window. I've added comments so you can tweak the rating-to-number mapping easily:

Sub ConvertRatingsToNumbers()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim rating As String
    Dim ratingValue As Integer
    
    ' Set your source and target sheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsTarget = ThisWorkbook.Sheets("Sheet3")
    
    ' Find the last row with data in Sheet1's column B (adjust if needed)
    lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
    
    ' Define your rating-to-number mapping (edit this to match your needs!)
    For i = 2 To lastRow ' Start at row 2 since your data starts at B2
        For j = 2 To 6 ' Columns B to F (B=2, F=6)
            rating = LCase(wsSource.Cells(i, j).Value) ' Convert to lowercase to avoid case issues
            
            ' Assign numeric value based on rating
            Select Case rating
                Case "low"
                    ratingValue = 1
                Case "medium"
                    ratingValue = 2
                Case "high"
                    ratingValue = 2 ' Matches your original formula's mapping for high
                Case "extreme"
                    ratingValue = 4 ' Adjust this to your preferred value
                Case Else
                    ratingValue = 0 ' Or set to blank with: ratingValue = ""
            End Select
            
            ' Write the value to Sheet3's corresponding cell
            wsTarget.Cells(i, j).Value = ratingValue
        Next j
    Next i
    
    MsgBox "Rating conversion complete!", vbInformation
End Sub

Quick Customization Tips: #

• Adjust Rating Values: Tweak the numbers in the Select Case block to match your exact ranking system.
• Case Insensitivity: Using LCase() ensures ratings like "Low" or "MEDIUM" are still recognized correctly.
• Handle Unknown Ratings: The Case Else sets unrecognized values to 0—swap that with "" if you prefer blank cells instead.

Step 3: Add a Button to Trigger the Script #

• Go back to Excel, switch to the Developer tab > Controls > Insert > Form Controls > Button (Form Control).
• Draw the button on your worksheet (anywhere convenient, like Sheet3 or Sheet1).
• In the "Assign Macro" window, select ConvertRatingsToNumbers and click OK.
• Rename the button (right-click > Edit Text) to something like "Convert Ratings".

Now just click that button whenever you need to batch convert all your ratings—no more dragging formulas across rows and columns!

内容的提问来源于stack exchange，提问作者Aaron