MATCH with VBA - Can anyone get it to work?? (download my test file )

[Keith]

Instead of numerous messages back and forth - maybe someone can help
me by downloading my small test file and editing one of the sample VBA
Sub's and Functions I placed in there.

Here is the file:

www.infocellar.com/test1.zip

I tried numerous code snippets from messages in here as to how to use
the Match function within Visual Basic for Excel. None have worked.

My test1.xls files is simple - two sheets. The first sheet, "CLEC" is
the one I want to find my matches in. The second sheet "Customer" has
the Match function in it. In the Customer sheet, Colum A is the
search terms, Columb B is the same terms with an * in front and after
the text so that it will work with partila matches, and Column C shows
the results of the Match function - which is working fine.

But the cell, D4 is where I am trying to use VBA to insert a result
from a Match subroutine or embedded custom function.

See if you can play around with it and email me the working test1.xls
- thats a heap !!! My email is:

(e-mail address removed)

 

[Jim Rech]

I don't see any evidence of a UDF that could work in your workbook since the
UDFs you have either take no inputs or a double as an input (and the
worksheet contains strings).

Anyway this UDF:

Function JimMatch(Item As Variant, Rg As Range) As Variant
JimMatch = Application.Match(Item, Rg, False)
End Function

used like this in the worksheet:

=JimMatch(B7,CLEC!A1:A9)

seems to work.

 

[Zbiq]

Instead of numerous messages back and forth - maybe someone can help
me by downloading my small test file and editing one of the sample VBA
Sub's and Functions I placed in there.

Here is the file:

www.infocellar.com/test1.zip

I tried numerous code snippets from messages in here as to how to use
the Match function within Visual Basic for Excel. None have worked.

My test1.xls files is simple - two sheets. The first sheet, "CLEC" is
the one I want to find my matches in. The second sheet "Customer" has
the Match function in it. In the Customer sheet, Colum A is the
search terms, Columb B is the same terms with an * in front and after
the text so that it will work with partila matches, and Column C shows
the results of the Match function - which is working fine.

But the cell, D4 is where I am trying to use VBA to insert a result
from a Match subroutine or embedded custom function.

See if you can play around with it and email me the working test1.xls
- thats a heap !!! My email is:

(e-mail address removed)

Sub Test1()
Sheets("Customer").Select
Range("d1") = "VBA match"
For d = 2 To Range(Range("a1"), Range("a1").End(xlDown)).Count
Match1 = "no match"
On Error Resume Next
Match1 = Application.WorksheetFunction.Match(Range("b" & d),
Worksheets("CLEC").Range("a1:a9"), 0)
Range("d" & d) = Match1
Next

End Sub

I hope that's what you're looking for.