Compile error, variable not defined.

[mageeg]

Probably a noob question, i am struggling with getting a piece of code to run.

I have a textbox in which in which the user enters a number, and if it is in between a certain range which is defined in a table, it responds and displays data in 2 different textboxes.

However see code below, I am getting a compile error on "for each i"

I can solve this by putting "Dim i As Range"

But then i get the same error on "Receiver Data" which is my worksheet name.

Private Sub TextBox4_AfterUpdate()

Dim ID As Long
Dim StartID As Long
Dim EndID As Long

If TextBox4.Value = "" Then

Else

    ID = TextBox4.Value
    
        For Each i In ReceiverData.Range("Table2[From]")
        
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
        
            If ID >= StartID And ID < EndID Then

            TextBox5.Value = i.Offset(0, 3).Value
            TextBox6.Value = i.Offset(0, 4).Value
            TextBox7.Value = i.Offset(0, 5).Value
        
            
         End If
        
        Next i

End If



End Sub

 

[Fluff]

If it's the sheet name use

For Each i In Sheets("ReceiverData").Range("Table2[From]")

 

[mageeg]

If it's the sheet name use

For Each i In Sheets("ReceiverData").Range("Table2[From]")

Hmm good thinking!

unfortunately now getting the error subscript out of range. Is it not finding the sheet now?

 

[Fluff]

Does the sheet name have a space in it?

 

[mageeg]

Does the sheet name have a space in it?

whoops my fault, messed up the spelling on receiver lol. Thanks man!

 

[Fluff]

You're welcome & thanks for the feedback.