Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup
I'm having trouble making the function work. I have coded the
following... Dim ProcCovered As String * 1 Dim PopGroupCol As Integer Dim MyRange As Range Set MyRange = Worksheets(2).Range("A1:AD173") PopGroupCol = 16 ProcCovered = Application.WorksheetFunction.VLookup(Worksheets(1 ).Cells(CurrRow, 4).Value, MyRange, PopGroupCol, False) I know that the value for which I am searching is in the range, so I would expect to get a value in ProcCovered. However, instead, I'm getting the following error: "1004 - Unable to get the Vlookup property of the WorksheetFunction class". OR...does anyone know another way that I can accomplish what I'm trying to do...using a value from one spreadsheet to look in the first column of another spreadsheet to find a match and then return a value from the same row, but in a different column. Help?!?! Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup
Sub Tester3()
Dim ProcCovered As String * 1 Dim PopGroupCol As Integer Dim MyRange As Range CurrRow = 4 Set MyRange = Worksheets(2).Range("A1:AD173") PopGroupCol = 16 ProcCovered = Application.WorksheetFunction. _ VLookup(Worksheets(1).Cells(CurrRow, 4).Value, _ MyRange, PopGroupCol, False) MsgBox ProcCovered End Sub worked fine for me when I defined what the value of CurrRow is. However, you will get an error such as this if the value is not found. You have to set up your code to handle this error. Sub Tester3() Dim ProcCovered As String * 1 Dim PopGroupCol As Integer Dim MyRange As Range CurrRow = 4 Set MyRange = Worksheets(2).Range("A1:AD173") PopGroupCol = 16 On Error Resume Next ProcCovered = Application.WorksheetFunction. _ VLookup(Worksheets(1).Cells(CurrRow, 4).Value, _ MyRange, PopGroupCol, False) If Err = 0 Then MsgBox ProcCovered Else MsgBox "Not found" End If On Error GoTo 0 End Sub -- Regards, Tom Ogilvy "CherylM" wrote in message ... I'm having trouble making the function work. I have coded the following... Dim ProcCovered As String * 1 Dim PopGroupCol As Integer Dim MyRange As Range Set MyRange = Worksheets(2).Range("A1:AD173") PopGroupCol = 16 ProcCovered = Application.WorksheetFunction.VLookup(Worksheets(1 ).Cells(CurrRow, 4).Value, MyRange, PopGroupCol, False) I know that the value for which I am searching is in the range, so I would expect to get a value in ProcCovered. However, instead, I'm getting the following error: "1004 - Unable to get the Vlookup property of the WorksheetFunction class". OR...does anyone know another way that I can accomplish what I'm trying to do...using a value from one spreadsheet to look in the first column of another spreadsheet to find a match and then return a value from the same row, but in a different column. Help?!?! Thanks! |
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 |