VLookup Not Available in VBA?

  • Thread starter Thread starter Ed Bitzer
  • Start date Start date
E

Ed Bitzer

I suspect, since not in the help file, that the VLookup function is not
available with a macro - true?
I am fairly new to Excel macos but not VBA under Access and was writing
one to grab the sheet name, Jan, Feb, Mar etc, determine the prior month
using VLookup and small table on a summary sheet, and then building cell
formula's which needed the prior month sheet. If I had used full names
of January, February I could use month number and then subtract and then
convert back. With abbreviation almost looks like I need to lean how to
"seek" as I use to do in Access. Appreciate if somebody would just give
me a push in the right direction.

Ed
 
Ed,

You can call most worksheet functions from VBA with code like

Result = Application.WorksheetFunction.VLookup(....)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
You can get to the vlookup worksheet function via the
application.worksheetfunction.vlookup or just application.vlookup.

If you use application.worksheetfunction.vlookup and the value isn't found,
it'll raise a trappable error. You can catch it like this:

Option Explicit
Sub testme03()
Dim res As Variant
Dim myVal As Variant
Dim myRng As Range

myVal = Worksheets("sheet1").Range("A1").Value
Set myRng = Worksheets("sheet2").Range("a1:b99")

On Error Resume Next
res = Application.WorksheetFunction.VLookup(myVal, myRng, 2, False)
If Err.Number <> 0 Then
MsgBox "not found"
Err.Clear
Else
MsgBox "found and equal to: " & res
End If
On Error GoTo 0

End Sub

But if you use application.vlookup, you can just inspect the results of the
vlookup.

Option Explicit
Sub testme02()
Dim res As Variant
Dim myVal As Variant
Dim myRng As Range

myVal = Worksheets("sheet1").Range("A1").Value
Set myRng = Worksheets("sheet2").Range("a1:b99")

res = Application.VLookup(myVal, myRng, 2, False)

If IsError(res) Then
MsgBox "not found"
Else
MsgBox "found and equal to: " & res
End If

End Sub

But even better, you may just be able to use the date to determine the month you
want:

Option Explicit
Sub testme01()

Dim myDate As Date
Dim myStr As String

myDate = Date

myStr = Format(DateSerial(Year(myDate), Month(myDate) - 1, 1), "mmm")
MsgBox myStr

myStr = MonthName(Month(myDate) - 1, abbreviate:=True)
MsgBox myStr

End Sub

I think the monthname function was added in xl2002.
 
Ed,

They enjoy it, as we all do, and I am sure that any suggestions re help were
not admonishing you, just trying to make it easier for you next time.
 
You can take advantage of the Vlookup function. The way it
is done is with a ststement of
application.worksheetfunction.vlookup(...). The (...)
would contain the same type arguments as the ones used
when typing the function in a cell. Example:
Malia=application.worksheetfunction.vlookup(A1,a40:f50,3).
Hope this helps!
 
Just to add:

You can also use Application.Vlookup, which returns a trappable error:

Dim varResult as Variant
varResult = Application.Vlookup("lookup_value",
Sheets("Sheet1").Range("A1:B10"),2,False)
If IsError (varResult) Then
'error
Else
'result
End If

This is an alternative to Application.Worksheetfunction.Vlookup.

Another way is to use Evaluate:

Debug.Print Application.Evaluate("VLOOKUP(""lookup_value"",
Sheet1!A1:B5,2,FALSE)")

Tim
 
Back
Top