VBA Vlookup struggle

[Mike54]

I would be greatful if someone can help me with line six of my code I just can't get it to work,. I'm trying to use VBA to do a lookup on sheet 3, I get the error "unable to get the vlookup property of the worksheetfunction class" which sadly dosen't mean much to me.

Private Sub Galley_Click()
Dim Team As Integer

If Galley.Value = True Then
Team = InputBox("Enter Team Number")
'Range("a21").Value = Team
Range("sheet1!D6") = Application.WorksheetFunction.VLookup(Team, ("sheet3!E20:H25"), 3, False)

Range("C6").Value = Now()
Range("C7").Value = ""
Range("B6").Value = "Search in Progress"
Range("B6").Interior.ColorIndex = 6
Range("B6").Font.ColorIndex = 1

Else

Range("C7").Value = Now()
Range("B6").Value = Range("C7").Value - Range("C6")
Range("B6").Interior.ColorIndex = 0
Range("B6").Font.ColorIndex = 1

If Range("B6").Value > 0 Then
Range("A6").Interior.ColorIndex = 43
Range("A6").Font.ColorIndex = 1
End If
End If
End Sub

Many thanks
Mike

 

[Weaver]

The syntax for the vlookup as used here is more properly:

= Application.WorksheetFunction.VLookup(Team, range("sheet3!E20:H25"), 3, False)

I'm not saying this'll make it work, as there could be other issues, depending on the data set you're using, but hopefully it'll help.

the use of the worksheetfunction, to me at least, seems notoriously difficult to debug, as the error messages offer very little in the way of constructive help. you should test them in a cell first but always remember that ranges have to be referenced in the VB way rather than the spreadsheet way when you port them over.

 

[Sal Paradise]

Range("sheet1!D6").FormulaR1C1 = "=VLOOKUP(" & Team & ", (Sheet3!E20:H25), 3, False)"

 

[Mike54]

Thanks Guys, I'm working my way through those ideas, Sal I plugged yours in and it returned #name, I also noticed that it actually enters the function, I hadn't considered that.
ExcelR8 Thanks still trying your idea