General discussion

Locked

VLookup function-Doesn't it work in VBA?

By malkor ·
I tried using Vlookup function in Excel VBA &, even though is shows up in both the Methods and the Completion listings, I couldn't get Excel to recognize it as a VBA function. Has anyone used this function _sucessfully_ in Excel VBA? mailto:malkor@moose-mail.com

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

VLookup function-Doesn't it work in VBA?

by awilliams3 In reply to VLookup function-Doesn't ...

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!

Collapse -

VLookup function-Doesn't it work in VBA?

by malkor In reply to VLookup function-Doesn't ...

Thanks much. It worked.

Collapse -

VLookup function-Doesn't it work in VBA?

by malkor In reply to VLookup function-Doesn't ...

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums