Vlookup syntax format

[Yossi evenzur]

Hi
the floowing line is coming from the excel help
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
How can i use this function in macro where the lookup_value is in A1 format,
the table_array is a variable (rng) ?
the lookup funtion is rffered to another sheet in the same workbook e.g.
sheet1 is where the function works but the reference array is in sheet2.

 

[Mike H]

Yossi,

Maybe:-

Sub stance()
Set rng = Sheets("Sheet2").Range("A1:B10")
lookupvalue = Sheets("Sheet1").Range("A1").Value
myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 2, False)
MsgBox myvalue
End Sub

Mike

 

[Yossi evenzur]

Hi
i haven't tested it yet but rng is changing for eacj workbook so i need the
macro to find it itself e.g. run "currentreggion" and get the range but i
can't make the syntax, can you help?

 

[Yossi evenzur]

Hi
Now i tested it, i modifyed the syntax a bit but i get run-time error 1004
"unable to get Vllookup property of the WorksheetFunction class

here is the syntax
Sub Add_TRF_CounterId()
Rows("1:1").Select
Selection.Insert Shift:=xlDown
' insert empyt row
Range("A1").Select
'select cell a1 to run vlookup

Set rng = Sheets("57, P_NBSC_SERVICE").Range("A2:c300")
lookupvalue = Sheets("Sheet 1").Range("A2").Value

'the next line gives the error massage
myvalue = Excel.WorksheetFunction.VLookup(lookupvalue, rng, 3, False)
End Sub

 

[Dave Peterson]

That means that there wasn't a match.

Option Explicit
Sub Add_TRF_CounterId()
dim lookupvalue as variant
dim res as variant

with worksheets("sheet 1")
.Rows(1).Insert
lookupvalue = .range("a1").value
end with

Set rng = Sheets("57, P_NBSC_SERVICE").Range("A2:c300")

res = application.VLookup(lookupvalue, rng, 3, False)

if iserror(res) then
res = "No Match"
end if

msgbox Res

End Sub

 

[Bill Martin]

Is there anything magic about doing this with dates in column A? I copied
Yossi's "stance" toy example and using integers in columns A and B all works
well. If I change the column A data to dates then the program always
reports the runtime error. This is whether the dates are typed simply as
text, or whether they're created by using a Date( ) function on the
worksheet.

On Sheet2 I have a column of consecutive dates. Then I do a copy/paste onto
Sheet1 so I know they are identical. Yet when I run the routine it
invariably fails with that same runtime error about "Unable to get the
Vlookup property of the WorksheetFunction class"

Can one not use VLookUp in VBA to search for dates, or am I overlooking
something fundamental? It works fine from the worksheet, just not from VBA.

Thanks.

Bill

-----------------

 

[Dave Peterson]

First, I used application.match (not application.worksheetfunction.match) and I
avoided any run time error. But I did test the result with an "if iserror(...)"
statement.

Second, sometimes converting the date to long will help:

dim res as variant
res = application.vlookup(clng(yourdatevar), yourrange, 2, false)
if iserror(res) then
'no match
else
msgbox "match on row: " & res
end if

 

[Dave Peterson]

Oops.

Make that application.vlookup() and application.worksheetfunction.vlookup()--but
they both suffer the same malady.

 

[Bill Martin]

Using "Application.Vlookup" does stop the runtime error. It returns a value
of "Error 2023" though which is not useful.

Abandoning the Vlookup function and going to Application.Match as you said
runs ok, and returns a value of "Error 2042". Typing the lookupvalue as
LONG or DOUBLE or SINGLE does seem to work properly. Using type STRING or
DATE returns the error code.

So I guess the bottom line is that I can build something up around MATCH and
it will work. Though it makes me very uneasy that other things which should
also work do not. I'm tempted to do a manual binary search to find the key
rather than relying on the VBA call. What to do, what to do....?

Anyhow, thanks for your help Dave. You nailed it.

(Incidentally, are we spinning our wheels using VBA anyhow now that
Microsoft says they're going to kill it? Basically VBA is the only reason
I've continued to be tied to Windows. If they kill that I may make the
break to Linux with Open Office or some such. Have you worked with the tool
Microsoft says we should be migrating to in place of VBA?)

Bill
------------------------------------

 

[Dave Peterson]

Checking the result of the application.vlookup() with iserror() should be
useful.

Did you try using application.vlookup(clng(yourdatevar), ... )?

Didn't they announce the killing of VBA for Macs?

And no, I haven't looked at any replacement language.

ps. I didn't mean to suggest abandoning application.vlookup(). But if clng()
didn't work, then that WAS going to be my followup suggestion!

Using "Application.Vlookup" does stop the runtime error. It returns a value
of "Error 2023" though which is not useful.

Abandoning the Vlookup function and going to Application.Match as you said
runs ok, and returns a value of "Error 2042". Typing the lookupvalue as
LONG or DOUBLE or SINGLE does seem to work properly. Using type STRING or
DATE returns the error code.

So I guess the bottom line is that I can build something up around MATCH and
it will work. Though it makes me very uneasy that other things which should
also work do not. I'm tempted to do a manual binary search to find the key
rather than relying on the VBA call. What to do, what to do....?

Anyhow, thanks for your help Dave. You nailed it.

(Incidentally, are we spinning our wheels using VBA anyhow now that
Microsoft says they're going to kill it? Basically VBA is the only reason
I've continued to be tied to Windows. If they kill that I may make the
break to Linux with Open Office or some such. Have you worked with the tool
Microsoft says we should be migrating to in place of VBA?)

Bill
------------------------------------

 

[Bill Martin]

What I read was that Microsoft wasn't putting VBA with the latest Mac Excel,
but don't worry -- they're also removing it from PC Excel with the next
release. As I recall, the effective date for removal of all support was
something like 2010.

However when I search the Microsoft web site now I don't find the stuff that
was there before about when support ends. Now I find this bit posted in a
Microsoft blog: http://blogs.msdn.com/excel/ (See the Jan 16 posting). It
claims they will not drop it from the PC.

I also found this link describing why it was too difficult to maintain VBA
on the Mac. Lots of assembly level stuff going on beneath the covers:
http://www.schwieb.com/blog/2006/08/08/saying-goodbye-to-visual-basic/

Anyhow, that's not an immediate concern. Thanks for the help Dave.

Bill

 

[Dave Peterson]

Thanks for the links. (I'm not too concerned about the rumors concerning wintel
PCs and VBA, either.)

What I read was that Microsoft wasn't putting VBA with the latest Mac Excel,
but don't worry -- they're also removing it from PC Excel with the next
release. As I recall, the effective date for removal of all support was
something like 2010.

However when I search the Microsoft web site now I don't find the stuff that
was there before about when support ends. Now I find this bit posted in a
Microsoft blog: http://blogs.msdn.com/excel/ (See the Jan 16 posting). It
claims they will not drop it from the PC.

I also found this link describing why it was too difficult to maintain VBA
on the Mac. Lots of assembly level stuff going on beneath the covers:
http://www.schwieb.com/blog/2006/08/08/saying-goodbye-to-visual-basic/

Anyhow, that's not an immediate concern. Thanks for the help Dave.

Bill

 

[Dave Peterson]

But you didn't answer my question...

Did application.vlookup(clng(yourdatevar), ...

work ok?

What I read was that Microsoft wasn't putting VBA with the latest Mac Excel,
but don't worry -- they're also removing it from PC Excel with the next
release. As I recall, the effective date for removal of all support was
something like 2010.

However when I search the Microsoft web site now I don't find the stuff that
was there before about when support ends. Now I find this bit posted in a
Microsoft blog: http://blogs.msdn.com/excel/ (See the Jan 16 posting). It
claims they will not drop it from the PC.

I also found this link describing why it was too difficult to maintain VBA
on the Mac. Lots of assembly level stuff going on beneath the covers:
http://www.schwieb.com/blog/2006/08/08/saying-goodbye-to-visual-basic/

Anyhow, that's not an immediate concern. Thanks for the help Dave.

Bill

 

[Bill Martin]

Sorry I skipped over responding to that question. I tried a variant of it
and it failed with VLOOKUP but worked with MATCH. What I did was to DIM the
key as LONG and then used that key for the search.

Since I still have the scrap of code though I went back and did it exactly
as you asked about. It still fails with VLOOKUP and still works with MATCH.

Bill
--------------------------------

 

[Dave Peterson]

This worked ok for me in xl2003:

Option Explicit
Sub testme()

Dim myDate As Date
Dim myRng As Range
Dim res As Variant

myDate = DateSerial(2008, 2, 3)
Set myRng = Worksheets("Sheet1").Range("A:E")

res = Application.VLookup(CLng(myDate), myRng, 2, False)

If IsError(res) Then
MsgBox "Not found"
Else
MsgBox res
End If

End Sub

Sorry I skipped over responding to that question. I tried a variant of it
and it failed with VLOOKUP but worked with MATCH. What I did was to DIM the
key as LONG and then used that key for the search.

Since I still have the scrap of code though I went back and did it exactly
as you asked about. It still fails with VLOOKUP and still works with MATCH.

Bill
--------------------------------

 

[Bill Martin]

Ok, your example works on my system (also Office 2003). So I set about
changing things in my other routine one line at a time and suddenly it
worked.

Turns out when I started dinking around with MATCH rather than VLOOKUP, I
changed the Rng from an array to a vector to make it work. However VLOOKUP
fails with a vector since it expects to get its result from another column.

Pilot error.

Bill
-------------------------------

 

[Dave Peterson]

I feel better!

And sorry about that initial typo with =match(). I sent you down the wrong path
by mistake!

Ok, your example works on my system (also Office 2003). So I set about
changing things in my other routine one line at a time and suddenly it
worked.

Turns out when I started dinking around with MATCH rather than VLOOKUP, I
changed the Rng from an array to a vector to make it work. However VLOOKUP
fails with a vector since it expects to get its result from another column.

Pilot error.

Bill
-------------------------------