工作表名称为数字时如何在VBA中正确调用？代码异常求助

Got it, let's break down what's happening here and fix it quickly.

The Root Problem #

VBA's Sheets() collection has a key quirk: it accepts two types of arguments:

• A number (treated as the worksheet's position/index in the workbook)
• A string (treated as the worksheet's name)

When you pull Range("H" & i).Value directly, if that cell contains a number (like 1, 2, 3), VBA passes it as a numeric value—so it's looking for the 1st, 2nd, 3rd worksheet by position, not the one named "1", "2", "3".

Your earlier attempt with Chr(34) added literal double quotes to the string (e.g., making it "1" instead of just 1), which doesn't match your actual worksheet name, hence the failure.

The Fix: Force a String Conversion #

You just need to convert the numeric value from the cell into a string so VBA treats it as a worksheet name instead of an index. There are a couple of simple ways to do this:

Option 1: Use the CStr() Function

This explicitly converts the value to a string:

For i = 5 To LastRow
    If Range("J" & i).Value = "NOT OK" Then
        ' Convert cell value to string to use as worksheet name
        Sheets(CStr(Range("H" & i).Value)).Tab.Color = RGB(255, 0, 0)
    End If
Next i

Option 2: Append an Empty String

This is a quick trick to coerce the value to a string without a function:

For i = 5 To LastRow
    If Range("J" & i).Value = "NOT OK" Then
        Sheets(Range("H" & i).Value & "").Tab.Color = RGB(255, 0, 0)
    End If
Next i

Option 3: Use a String Variable (For Clarity)

If you want to make the code easier to read and debug, store the worksheet name in a string variable first:

Dim i As Long
Dim LastRow As Long
Dim targetSheetName As String

' Make sure you're setting LastRow correctly (example below)
LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row

For i = 5 To LastRow
    If Range("J" & i).Value = "NOT OK" Then
        targetSheetName = CStr(Range("H" & i).Value)
        Sheets(targetSheetName).Tab.Color = RGB(255, 0, 0)
    End If
Next i

Bonus: Add Error Handling (Optional) #

To avoid crashes if a value in column H doesn't match an existing worksheet name, you can add basic error handling:

For i = 5 To LastRow
    If Range("J" & i).Value = "NOT OK" Then
        On Error Resume Next ' Skip errors if sheet doesn't exist
        Sheets(CStr(Range("H" & i).Value)).Tab.Color = RGB(255, 0, 0)
        On Error GoTo 0 ' Reset error handling
    End If
Next i

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