I feel better!

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

Bill Martin wrote:

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" wrote in message
...
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




Bill Martin wrote:

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" wrote in message
...
But you didn't answer my question...

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

work ok?

Bill Martin wrote:

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/...-visual-basic/

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

Bill

"Dave Peterson" wrote in message
...
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!

Bill Martin wrote:

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" wrote in message
...
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


Bill Martin wrote:

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" wrote in message
...
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

Yossi evenzur wrote:

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

"Mike H" wrote:

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" wrote:

Hi
the floowing line is coming from the excel help
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_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.

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson