Match problem

U

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
 
U

utkarsh.majmudar

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

Utkarsh
 
D

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
 
T

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
 
U

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top