VLookup Not Available in VBA?
 

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

VLookup Not Available in VBA?
 

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




"Ed Bitzer" wrote in message
...
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

VLookup Not Available in VBA?
 

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 Bitzer wrote:

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

--

Dave Peterson

VLookup Not Available in VBA?
 

Hi Ed

Application.WorksheetFunction.VLookup(......

In the VBA you use it like this

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ed Bitzer" wrote in message ...
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

VLookup Not Available in VBA?
 

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.

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Ed Bitzer" wrote in message
...
Ron, Chip and Dave,

You guys are spoiling me with quick answers. Now understand I did look
diligently in the VBA help but for the wrong thing and also searched
unsuccessfully several Excel sites with examples. Ron has already
helped me the last several days so he knows I am new at this but should
know better. However my programming breath always finds me jumping into
a new area with both feet without preparation - and being retired I'll
try anything, because time is available.

Thanks again,

Ed

VLookup Not Available in VBA?
 

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!
-----Original Message-----
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


.

VLookup Not Available in VBA?
 

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

"Ed Bitzer" wrote in message
...
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