VBA / VLOOKUP Problem - please help.

[Guest]

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.

 

[Bob Phillips]

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)

 

[Nigel]

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(strNodeID, _
sc1, 5, False))

 

[Nigel]

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

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(strNodeID, _
sc1, 5, False))


--
Cheers
Nigel

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.

 

[Guest]

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.

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

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(strNodeID, _
sc1, 5, False))


--
Cheers
Nigel

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.