Solved: how to use match property of the worksheetfunction class?

[maryam]

I use this coding:
dim i as long
for i=1 to 10
k = Application.WorksheetFunction.Match_
(Worksheets("sheet1").Range("AD" & i),_
Worksheets("sheet2").Range("B1:IV1"), 0)
next
but I get run time error 1004, unable to get the match property.
Can u help me please?:banghead:

[Bob Phillips]

I haven't tested it, no idea what the data looks like, but this shoule be all that yiu need



Dim i As Long
For i = 1 To 10
k = Application.Match(Worksheets("sheet1").Range("AD" & i), Works, 0)
Next k

[Simon Lloyd]

Normally your worksheet function used in VBA will still take the same form of the actual worksheet function
=Match(Lookup Value, Lookup Range,match type)
match type is false or 0 for an exact match and True or 1 for a nearest match

[Simon Lloyd]

Ooops, didn't want to tread on toes, i see Xld has already posted!

[Bob Phillips]

There is room for all!

[maryam]

you mean :

k= Application.Match(Worksheets("sheet1").Range("AD" & i), Worksheets("sheet2").Range("B1:IV1"), 0)

it gives error 13, type mismatch

[Simon Lloyd]

Thsi worked fine for me

Dim i As Integer
For i = 1 To 10
k = Application.Match(Worksheets("sheet1").Range("AD" & i), Worksheets("sheet2").Range("B1:IV1"), 0)
Next i

[JKwan]

you mean :

k= Application.Match(Worksheets("sheet1").Range("AD" & i), Worksheets("sheet2").Range("B1:IV1"), 0)

it gives error 13, type mismatch

This will explain why you are getting an error
http://www.cpearson.com/excel/CallingWorksheetFunctionsFromVBA.htm

[maryam]

no sorry now it works fine. I didnt have value in the worksheet cells.