Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH with VBA - Can anyone get it to work?? (download my test file )
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: |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH with VBA - Can anyone get it to work?? (download my test file )
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. -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH with VBA - Can anyone get it to work?? (download my test file )
Keith < wrote in message . ..
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: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
download a template excel Calendar into a work sheet? | Excel Discussion (Misc queries) | |||
download or paste a calendar in a work sheet? | Excel Discussion (Misc queries) | |||
previous download doesn't work | Excel Discussion (Misc queries) | |||
compare and match test | Excel Worksheet Functions | |||
Tried to download SP3 no disc now Excel does not work | Excel Discussion (Misc queries) |