worksheetfunction.match

Posted by Tommy Bak on February 07, 2002 12:30 PM

Hi
what am I doing wrong here:
Sub testing()
testdate = CDate("14-06-1998")
x = Application.WorksheetFunction.Match(testdate, Range("a1:a25"), 0)
MsgBox (x)
End Sub

Range A1:A25 has reel dates, and are formatted as dates, but I keep getting an error 1004 and stop in the worksheetfunction line.
Can this function not operate with dates??
If I insert numbers, there is no problems.

Tommy

Posted by Mark W. on February 07, 2002 12:34 PM

On what worksheet does A1:A25 contain dates? [nt]

Posted by Juan Pablo G. on February 07, 2002 12:36 PM

This is the same as getting #N/A in Excel. Are you sure the date exists ?

Juan Pablo G.

Posted by Tommy Bak on February 07, 2002 12:46 PM

Re: On what worksheet does A1:A25 contain dates? [nt]

Dates are on worksheet "Ark1"
I've changed the sub to
Sub testing()
testdate = Worksheets("Ark1").Range("B1")
x = Application.WorksheetFunction.Match(testdate, Worksheets("Ark1").Range("a1:a25"), 0)
MsgBox (x)
End Sub

and put the searchdate in cell B1.
Still error 1004

Tommy

Posted by Mark W. on February 07, 2002 1:00 PM

But this works...

Sub testing()
x = Application.WorksheetFunction.Match(Worksheets("Ark1").Range("B1"), Worksheets("Ark1").Range("a1:a25"), 0)
MsgBox (x)
End Sub

Posted by Tommy Bak on February 07, 2002 1:08 PM

Re: But this works...

Thanks Mark.
It works for too and i'm happy again.
But what is the matter with the first example??
The reason i ark is that I'm trying to get the searchdate via an inputbox.
Tommy

Posted by Juan Pablo G. on February 07, 2002 1:20 PM

Ok, got it to work... this is VERY strange. This way It DIDN'T work.

Sub Test()
Dim testdate As Date
testdate = Worksheets("Ark1").Range("B1")
x = Application.Match(testdate, Worksheets("Ark1").Range("A1:A25"))
MsgBox CStr(x)
End Sub

This way it DID work

Sub Test()
Dim testdate As Long
testdate = Worksheets("Ark1").Range("B1")
x = Application.Match(testdate, Worksheets("Ark1").Range("A1:A25"))
MsgBox CStr(x)
End Sub

Very strange, as I said...

Juan Pablo G.

Posted by Mark W. on February 07, 2002 1:21 PM

Tommy, I'm not a power VBA-er, but...

I suspect that the assignment of a date value
to the variable, 'testvalue', isn't working.
Would you need to declare the data type of
'testvalue' somewhere before you used it?

Posted by Mark W. on February 07, 2002 1:21 PM

Sorry, make that 'testdate' instead of 'testvalue' [nt]

Posted by Mark W. on February 07, 2002 1:23 PM

I thing Juan Pablo G. just proved me right... see above [nt]

Posted by Tommy Bak on February 07, 2002 1:35 PM

Thanks to both of you.

Thanks again
Now it work as i want it to, but as juan Pablo says "Strange"
the code is now:

Sub Test()
Dim testdate As Long
testdate = CDate(InputBox("Input your date:"))
x = Application.Match(testdate, Worksheets("Ark1").Range("A1:A25"), 0)
MsgBox x
End Sub