Match problem

[utkarsh.majmudar]

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") are:
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

 

[utkarsh.majmudar]

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

 

[Don Guillett]

why not use FIND instead of a worksheet function.

 

[utkarsh.majmudar]

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

Utkarsh

 

[Don Guillett]

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

 

[Tom Ogilvy]

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

 

[utkarsh.majmudar]

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?

 

[Tom Ogilvy]

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