VBA 424 error

[hailstorm]

Hello there

Could you guys tell me how to fix this error, i tried sheet2.activate but i did not worked. It says "Error 424, object not found."

Private Sub Reg1_AfterUpdate()
'Check to see if value exists
[COLOR=rgb(85, 57, 130)]If WorksheetFunction.CountIf(Sheet2.Range("B:B"), Me.Reg1.Value) = 0 Then  <-- Error here[/COLOR]
MsgBox "This is an incorrect ID"
Me.Reg1.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 2, 0)     <-- Error here
.Reg3 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 3, 0)
.Reg4 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 4, 0)
.Reg5 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 5, 0)
.Reg6 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 6, 0)
End With
End Sub

 

[hailstorm]

Ignore the


it is not in the code.

 

[steve the fish]

Do you have a Sheet2? This is referring to a sheet code name not a sheet name by the way. Go to the VBA window and look in the project explorer. The sheet code name is the name not in the brackets under microsoft excel objects.

 

[Fluff]

Try

Sheets("Sheet2").Range("Lookup")