Excel VBA - Run-time error 438 - When pulling a value using a For Loop

Question

I am trying to pull a string from a range of cells in a Sheet to test in an If statement to determine which action to take. Debug highlights the below line as the cause of the error:

testValue = Sheets("Issue Reference").testRange.Value

but I can't figure out why that is a problem, maybe something with the .Value property? Any ideas? Entire code below:

Private Sub SubGroupList_Click()
Dim selection As String
Dim rng As Range
Dim testRange As Range
Dim i As Integer
Dim testValue As String

selection = SubGroupList.Value

If failedComponentTrack = 1 Then
    Set rng = Range("C3:C10")

ElseIf failedComponentTrack = 2 Then
    Set rng = Range("E3:E10")

ElseIf failedComponentTrack = 3 Then
    Set rng = Range("G3:G10")

Else

End If

i = 1
For Each testRange In rng
    testValue = Sheets("Issue Reference").testRange.Value
    If selection = testValue Then
        Sheets("DataStore").Cells(LB, 27).Value = selection
        failedComponentTrack = i
    End If
    i = i + 1
Next testRange
End Sub

Answer 1

This should work. Use the worksheets reference on your set rng statements. This can be cleaned up, but I think you understand the solution better this way.

The Reference to your source sheet is already inside of yout rng variable. Therefore, there is no need to reference again in your for each loops condition.

The Syntax here is wrong

testValue = Sheets("Issue Reference").testRange.Value

because the Worksheet Object Sheets("Issue Reference") has no testRange property.

This is the corrected version of your code:

Private Sub SubGroupList_Click()
    Dim selection As String
    Dim rng As Range
    Dim testRange As Range
    Dim i As Integer
    Dim testValue As String
    
    selection = SubGroupList.Value
    
    If failedComponentTrack = 1 Then
        Set rng = Sheets("Issue Reference").Range("C3:C10")
    
    ElseIf failedComponentTrack = 2 Then
        Set rng = Sheets("Issue Reference").Range("E3:E10")
    
    ElseIf failedComponentTrack = 3 Then
        Set rng = Sheets("Issue Reference").Range("G3:G10")
    
    Else
    
    End If
    
    i = 1
    For Each testRange In rng
        testValue = testRange.Value
        If selection = testValue Then
            Sheets("DataStore").Cells(LB, 27).Value = selection
            failedComponentTrack = i
        End If
        i = i + 1
    Next testRange
End Sub