Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Error
I am trying to captures the contents of a field in a worksheet based on the
value of a field in a different worksheet. This field will become the To list in an email message but I am receiving an error in the Vlookup function: Dim CCList As String Dim tmplist As String Dim LkRange As Range Set LkRange = Worksheets("Contact List").Range("A2:F25") If Sheets("Hold Reasons").Range("C28") = "X" Then tmplist = Application.WorksheetFunction.VLookup(D6, LkRange, 2, False) CCList = CCList + tmplist End If The above code generates the error "Run Time Error 1004; Unable to get the Vlookup property of the Worksheet Function class" on the Vlookup line. When stepping through this code LkRange has no value so the failure appears to be in capturing the range. I tried putting the range directly into the Vlookup function and that generated syntax errors. Any help would be appreciated! Marnie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Error
Range worked!
Thanks! "Don Guillett" wrote: range("d6") or use vba FIND instead along with OFFSET -- Don Guillett Microsoft MVP Excel SalesAid Software "Marnie" wrote in message ... I am trying to captures the contents of a field in a worksheet based on the value of a field in a different worksheet. This field will become the To list in an email message but I am receiving an error in the Vlookup function: Dim CCList As String Dim tmplist As String Dim LkRange As Range Set LkRange = Worksheets("Contact List").Range("A2:F25") If Sheets("Hold Reasons").Range("C28") = "X" Then tmplist = Application.WorksheetFunction.VLookup(D6, LkRange, 2, False) CCList = CCList + tmplist End If The above code generates the error "Run Time Error 1004; Unable to get the Vlookup property of the Worksheet Function class" on the Vlookup line. When stepping through this code LkRange has no value so the failure appears to be in capturing the range. I tried putting the range directly into the Vlookup function and that generated syntax errors. Any help would be appreciated! Marnie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Error
You may still have a problem. If the value in D6 doesn't match anything in
A2:A25 of Contact list, you'll get the same error. I'd use: Dim CCList As String Dim tmplist As Variant 'could return an error Dim LkRange As Range Set LkRange = Worksheets("Contact List").Range("A2:F25") If Sheets("Hold Reasons").Range("C28") = "X" Then 'dropped the .worksheetfunction portion tmplist = Application.VLookup(somesheet.range("D6"), LkRange, 2, False) if iserror(tmplist) then tmplist = 0 end if CCList = CCList + tmplist End If I'd qualify where D6 is located, too. Marnie wrote: I am trying to captures the contents of a field in a worksheet based on the value of a field in a different worksheet. This field will become the To list in an email message but I am receiving an error in the Vlookup function: Dim CCList As String Dim tmplist As String Dim LkRange As Range Set LkRange = Worksheets("Contact List").Range("A2:F25") If Sheets("Hold Reasons").Range("C28") = "X" Then tmplist = Application.WorksheetFunction.VLookup(D6, LkRange, 2, False) CCList = CCList + tmplist End If The above code generates the error "Run Time Error 1004; Unable to get the Vlookup property of the Worksheet Function class" on the Vlookup line. When stepping through this code LkRange has no value so the failure appears to be in capturing the range. I tried putting the range directly into the Vlookup function and that generated syntax errors. Any help would be appreciated! Marnie -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup error | Excel Worksheet Functions | |||
VLOOKUP error | Excel Discussion (Misc queries) | |||
Vlookup Error | Excel Programming | |||
Vlookup Error | Excel Worksheet Functions | |||
vlookup error | Excel Worksheet Functions |