Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |