| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA / VLOOKUP Problem - please help.
Hiya,
Yes, I really am tearing my hair out. I have read many posts on here in relation to VLOOKUP, and none of the suggestions work, so please have a look at this.... I very simply want to use VLOOKUP from VBA to find nodal forces from a table, given the nodal ID. Here is the code I have tried.... [VBA Code starts here] Dim strNodeID As String Dim FX As Variant strNodeID = InputBox("Enter node to find: ", "Enter Node ID", "38") FX = Application.WorksheetFunction.VLookup(strNodeID, _ sc1, 5, False) MsgBox "For node ID : " & strNodeID & vbCrLf & _ "FX = " & FX, vbOKOnly, "Eureka!" [VBA Code ends here] Referring to the code, "sc1" is a named range. I have also tried the following variants on the range:- SC1 "SC1" Range(SC1) Range("SC1") "SC01!$A$4:$J$2908" Range("SC01!$A$4:$J$2908") I am at my wits end. The error message I get is one of :- 1004: run time error, "Unable to get the VLookup property of the WorksheetFunction class" 1004: run time error, "Method 'Range' of object '_Worksheet' failed I have tried the INDEX function in the past alsdo with no joy. Is my only option to use the Range.Find method, or is my version of Excel duff (or me!!) - I'm running Excel 2003 (11.6355.6360) SP1, part of Office Prof Ed 2003. Any help will be appreciated. -- Cheers, Paul. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA / VLOOKUP Problem - please help.
If sc1 is a range, try
FX = Application.VLookup(strNodeID, Range("sc1"), 5, False) -- HTH RP (remove nothere from the email address if mailing direct) "evildad" wrote in message ... Hiya, Yes, I really am tearing my hair out. I have read many posts on here in relation to VLOOKUP, and none of the suggestions work, so please have a look at this.... I very simply want to use VLOOKUP from VBA to find nodal forces from a table, given the nodal ID. Here is the code I have tried.... [VBA Code starts here] Dim strNodeID As String Dim FX As Variant strNodeID = InputBox("Enter node to find: ", "Enter Node ID", "38") FX = Application.WorksheetFunction.VLookup(strNodeID, _ sc1, 5, False) MsgBox "For node ID : " & strNodeID & vbCrLf & _ "FX = " & FX, vbOKOnly, "Eureka!" [VBA Code ends here] Referring to the code, "sc1" is a named range. I have also tried the following variants on the range:- SC1 "SC1" Range(SC1) Range("SC1") "SC01!$A$4:$J$2908" Range("SC01!$A$4:$J$2908") I am at my wits end. The error message I get is one of :- 1004: run time error, "Unable to get the VLookup property of the WorksheetFunction class" 1004: run time error, "Method 'Range' of object '_Worksheet' failed I have tried the INDEX function in the past alsdo with no joy. Is my only option to use the Range.Find method, or is my version of Excel duff (or me!!) - I'm running Excel 2003 (11.6355.6360) SP1, part of Office Prof Ed 2003. Any help will be appreciated. -- Cheers, Paul. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA / VLOOKUP Problem - please help.
Hi
Vlookup is probably expecting a numerical value and the inputbox is returning a string. Change the dim statement to..... Dim strNodeID As Long and the Input statement to..... FX = Val(Application.WorksheetFunction.VLookup(strNodeI D, _ sc1, 5, False)) -- Cheers Nigel "evildad" wrote in message ... Hiya, Yes, I really am tearing my hair out. I have read many posts on here in relation to VLOOKUP, and none of the suggestions work, so please have a look at this.... I very simply want to use VLOOKUP from VBA to find nodal forces from a table, given the nodal ID. Here is the code I have tried.... [VBA Code starts here] Dim strNodeID As String Dim FX As Variant strNodeID = InputBox("Enter node to find: ", "Enter Node ID", "38") FX = Application.WorksheetFunction.VLookup(strNodeID, _ sc1, 5, False) MsgBox "For node ID : " & strNodeID & vbCrLf & _ "FX = " & FX, vbOKOnly, "Eureka!" [VBA Code ends here] Referring to the code, "sc1" is a named range. I have also tried the following variants on the range:- SC1 "SC1" Range(SC1) Range("SC1") "SC01!$A$4:$J$2908" Range("SC01!$A$4:$J$2908") I am at my wits end. The error message I get is one of :- 1004: run time error, "Unable to get the VLookup property of the WorksheetFunction class" 1004: run time error, "Method 'Range' of object '_Worksheet' failed I have tried the INDEX function in the past alsdo with no joy. Is my only option to use the Range.Find method, or is my version of Excel duff (or me!!) - I'm running Excel 2003 (11.6355.6360) SP1, part of Office Prof Ed 2003. Any help will be appreciated. -- Cheers, Paul. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA / VLOOKUP Problem - please help.
Should of added use the following to declare your range......
Dim sc1 as Range Set sc1 = Worksheets("SC01").Range("A4:J2908") Assuming the range is on a worksheet named SC01 ? -- Cheers Nigel "Nigel" wrote in message ... Hi Vlookup is probably expecting a numerical value and the inputbox is returning a string. Change the dim statement to..... Dim strNodeID As Long and the Input statement to..... FX = Val(Application.WorksheetFunction.VLookup(strNodeI D, _ sc1, 5, False)) -- Cheers Nigel "evildad" wrote in message ... Hiya, Yes, I really am tearing my hair out. I have read many posts on here in relation to VLOOKUP, and none of the suggestions work, so please have a look at this.... I very simply want to use VLOOKUP from VBA to find nodal forces from a table, given the nodal ID. Here is the code I have tried.... [VBA Code starts here] Dim strNodeID As String Dim FX As Variant strNodeID = InputBox("Enter node to find: ", "Enter Node ID", "38") FX = Application.WorksheetFunction.VLookup(strNodeID, _ sc1, 5, False) MsgBox "For node ID : " & strNodeID & vbCrLf & _ "FX = " & FX, vbOKOnly, "Eureka!" [VBA Code ends here] Referring to the code, "sc1" is a named range. I have also tried the following variants on the range:- SC1 "SC1" Range(SC1) Range("SC1") "SC01!$A$4:$J$2908" Range("SC01!$A$4:$J$2908") I am at my wits end. The error message I get is one of :- 1004: run time error, "Unable to get the VLookup property of the WorksheetFunction class" 1004: run time error, "Method 'Range' of object '_Worksheet' failed I have tried the INDEX function in the past alsdo with no joy. Is my only option to use the Range.Find method, or is my version of Excel duff (or me!!) - I'm running Excel 2003 (11.6355.6360) SP1, part of Office Prof Ed 2003. Any help will be appreciated. -- Cheers, Paul. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA / VLOOKUP Problem - please help.
Cheers for the help guys. Yes, it works ;-)
As Nigel pointed out, VLookup expects a number to search for - I have set the value to find as a long, not as a string and it works - also using CInt(strNodeID... in the VLookup statement works. I have also deleted the named range "sc1" and used the Dim sc1 As Range : Set sc1 = "A4:J2908" method of decalring the range to search. Thanks again for the help, Paul Fenton. "Nigel" wrote: Should of added use the following to declare your range...... Dim sc1 as Range Set sc1 = Worksheets("SC01").Range("A4:J2908") Assuming the range is on a worksheet named SC01 ? -- Cheers Nigel "Nigel" wrote in message ... Hi Vlookup is probably expecting a numerical value and the inputbox is returning a string. Change the dim statement to..... Dim strNodeID As Long and the Input statement to..... FX = Val(Application.WorksheetFunction.VLookup(strNodeI D, _ sc1, 5, False)) -- Cheers Nigel "evildad" wrote in message ... Hiya, Yes, I really am tearing my hair out. I have read many posts on here in relation to VLOOKUP, and none of the suggestions work, so please have a look at this.... I very simply want to use VLOOKUP from VBA to find nodal forces from a table, given the nodal ID. Here is the code I have tried.... [VBA Code starts here] Dim strNodeID As String Dim FX As Variant strNodeID = InputBox("Enter node to find: ", "Enter Node ID", "38") FX = Application.WorksheetFunction.VLookup(strNodeID, _ sc1, 5, False) MsgBox "For node ID : " & strNodeID & vbCrLf & _ "FX = " & FX, vbOKOnly, "Eureka!" [VBA Code ends here] Referring to the code, "sc1" is a named range. I have also tried the following variants on the range:- SC1 "SC1" Range(SC1) Range("SC1") "SC01!$A$4:$J$2908" Range("SC01!$A$4:$J$2908") I am at my wits end. The error message I get is one of :- 1004: run time error, "Unable to get the VLookup property of the WorksheetFunction class" 1004: run time error, "Method 'Range' of object '_Worksheet' failed I have tried the INDEX function in the past alsdo with no joy. Is my only option to use the Range.Find method, or is my version of Excel duff (or me!!) - I'm running Excel 2003 (11.6355.6360) SP1, part of Office Prof Ed 2003. Any help will be appreciated. -- Cheers, Paul. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Problem | Excel Worksheet Functions | |||
| VLOOKUP problem | Excel Programming | |||
| VLOOKUP Problem | Excel Programming | |||
| vlookup problem | Excel Programming | |||
Linear Mode
