Match problem
 

Hi
The following code is misbehaving and throwing up an error 1004.
************
Sub match_add()
Dim v1 As Date, r1 As Range, m1 As Variant


v1 = Sheets("Sheet1").Range("d1").Value
Set r1 = Sheets("Sheet2").Range("a3:a18")
m1 = Application.Match(v1, r1, 0)
MsgBox m1

End Sub
*************
The value in cell d1 on Sheet1 is 5/1/06. And the values on Sheet2
Range('a3:a18") a
1/1/06
2/1/06
3/1/06
4/1/06
4/1/06
5/1/06
5/1/06
5/1/06
6/1/06
7/1/06
8/1/06
9/1/06
10/1/06
12/1/06
13/1/06
14/1/06
which certainly includes the value contained in cell d1. The match
function in excel gives the correct result -- so its not a case of a
missing value. I'm stumped!!! Please help.

Utkarsh

Match problem
 

Oops...the error is 2042 for the variable m1. Sorry about that.

Match problem
 

why not use FIND instead of a worksheet function.

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Hi
The following code is misbehaving and throwing up an error 1004.
************
Sub match_add()
Dim v1 As Date, r1 As Range, m1 As Variant


v1 = Sheets("Sheet1").Range("d1").Value
Set r1 = Sheets("Sheet2").Range("a3:a18")
m1 = Application.Match(v1, r1, 0)
MsgBox m1

End Sub
*************
The value in cell d1 on Sheet1 is 5/1/06. And the values on Sheet2
Range('a3:a18") a
1/1/06
2/1/06
3/1/06
4/1/06
4/1/06
5/1/06
5/1/06
5/1/06
6/1/06
7/1/06
8/1/06
9/1/06
10/1/06
12/1/06
13/1/06
14/1/06
which certainly includes the value contained in cell d1. The match
function in excel gives the correct result -- so its not a case of a
missing value. I'm stumped!!! Please help.

Utkarsh

Match problem
 

Find does not work. Find will only search for a text item withing a
text.

Utkarsh

Match problem
 

I meant to use the VBA FIND function.

Sub match_add()'Yours FIXED
Dim v1 As Range, r1 As Range
Set v1 = Sheets("Sheet3").Range("d1")
Set r1 = Sheets("Sheet3").Range("e3:e18")
MsgBox Application.Match(v1, r1, 0)
End Sub

Sub find_add()' BETTER
With Sheets("sheet3")
MsgBox .Columns("e").Find(.Range("d1")).Row - 2
End With
End Sub
--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Find does not work. Find will only search for a text item withing a
text.

Utkarsh

Match problem
 

Sub match_add()
Dim v1 As Date, r1 As Range, m1 As Variant
v1 = Sheets("Sheet1").Range("d1").Value
Set r1 = Sheets("Sheet2").Range("a3:a18")
' use clng(v1)
m1 = Application.Match(clng(v1), r1, 0)
if iserror(m1) then
msgbox "Not found"
else
MsgBox "found at row " & m1 + 2
end IF
End Sub

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Oops...the error is 2042 for the variable m1. Sorry about that.

Match problem
 

Thanks Tom and Don for your effort and help. Tom's solution worksbetter
for me in this situation.

By the way, Tom, what does "clng" do?

Match problem
 

It converts the date to a Long and in my experience, allows match to work.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Thanks Tom and Don for your effort and help. Tom's solution worksbetter
for me in this situation.

By the way, Tom, what does "clng" do?