VLookup function-Doesn't it work in VBA?

A sample (created in Excel 97) in a new workbook. On sheet1.

A1: STATE B1: CAPITAL
A2: AZ B2: PHOENIX
A3: CA B3: SACRAMENTO
A4: CO B4: DENVER
A5: NC B5: RALEIGH
A6: SC B6: COLUMBIA

Insert the following code in to a module.
Public Sub vlookupsample()
Dim rngLookup As Excel.Range
Dim strStateToLookup As String
Dim strCapital As String

strStateToLookup = InputBox(prompt:="Which State?")

If strStateToLookup = "" Then
Exit Sub
End If

Set rngLookup = Worksheets("Sheet1").Cells(1, 1).CurrentRegion

strCapital = ""

On Error Resume Next
strCapital = Application.WorksheetFunction.VLookup(strStateToLookup, _
rngLookup, 2, False)
On Error GoTo 0

If strCapital = "" Then
MsgBox prompt:=strStateToLookup & " not found!"
Else
MsgBox prompt:="The capital of " & strStateToLookup & " is " & strCapital
End If

Set rngLookup = Nothing
End Sub

Try it out. Two things that can be causing your problems. One, VLOOKUP will actually cause a VBA error if the value you are looking up is not found. Notice I use a basic error handler along with returning the result to a variable. I know the variable will remain empty if an error occurs.

Second, syntax. You do not use the worksheet syntax when referring to a range as a functions argument. You use VBA syntax and supply a range object. Such as Worksheets("Sheet1").Range("A1:B6").

Good luck!