Error in match through VBA

[kaushik03]

Hi all,

I am getting an error while doing match for date.

In the workbook attached, I am trying to find the date stored in I2 in the range A1 to Z1, but getting an error.(see the code in mod1).

Could you plz fix it and let me know the reason!

Kaushik

 

[NARAYANK991]

Hi Kaushik ,

Try this code :

Sub Test()
    Dim X As Long
    X = Sheets("Sheet1").Range("I10").Value

    y = Application.WorksheetFunction.Match(X, Sheets("Sheet1").Range("A1:Z1"), 0)
End Sub

As an alternative , you can also use a conversion function as follows :

Sub Test()
    X = Sheets("Sheet1").Range("I10").Value

    y = Application.WorksheetFunction.Match(CLng(X), Sheets("Sheet1").Range("A1:Z1"), 0)
End Sub

The problem is that if you do not explicitly declare the type of a variable , by default it is a Variant ; in this case , as a consequence of the assignment in the following statement :

X = Sheets("Sheet1").Range("I10").Value

X becomes a variable of Date type ; consequently , in the MATCH statement , the VBA Date type variable cannot match with the dates in the range A1:Z1 ( in which the values are just numbers ; remember that in Excel dates are actually numbers ).

Declaring the variable X as of type Long enables the MATCH to work , since the types of X and the dates in A1:Z1 now are identical.

Similarly , the forced conversion using the CLng function converts the Date type variable to one of Long type.

Narayan

 

[kaushik03]

It worked...thanks Narayan.....you are brilliant...

But just curious...earlier I did the same without declaring the variable type and it worked...could you please explain me the logic behind!

Regards,
Kaushik