Reveal all matches with FIND function

[ElizabethC]

I’ve just entered the wonderful world of VBAs and I’ve managed to create this great partial Find function that saves a lot of scrolling and manual filtering. Basically, in my non-technical language, it looks for partial matches by comparing a list of names with a column of other, slightly different names in another sheet. After doing so, it fills in a column next to the originally searched names with prices which were taken from adjacent cells of the partially matching cells from the other sheet (see formula below). Only thing is, there are multiple matches for one name and hence I would like the formula to find all the matches and then sum together the prices (from the adjacent cells next to the matches). I’ve searched and even tried to write something of my own, but to no avail. I feel like it shouldn’t be very difficult to alter this macros so that it continues searching till it finds all the matches, but I’m just not getting anywhere by myself.

If you could help, I’d be ever so grateful!



Sub FindVLookup()



Dim rngSearch As Range, rngOrdernames As Range, rngFound As Range, rngOrders As Range







Set rngSearch = Sheets("List1").Range("B1:B400")



Set rngOrdernames = Sheets("ALL JOBS JANUARY").Range("C3:C300")





'searches for all order names mentioned in cells C3 to cells C300:



For Each rngOrders In rngOrdernames



'order names are searched in Range("B1:B400"):



Set rngFound = rngSearch.Find(What:=rngOrders, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)



'if order name is found:



If Not rngFound Is Nothing Then



'found order's costs are entered in column B, against his name given in column A:



rngOrders.Offset(0, 1) = rngFound.Offset(0, 2)



End If



Next

 

[jasonb75]

Welcome to MrExcel.

I have to ask why you're trying to do this in vba? There is a perfectly good worksheet function called SUMIFS that should be able to complete the task just fine.

For future reference, please remember to post any code with code tags, the easiest way is to click the </> icon on the reply box toolbar the paste your code to the popup window.
I don't think that it is an official rule here, but it is preferred by most.

 

[ElizabethC]

Welcome to MrExcel.

I have to ask why you're trying to do this in vba? There is a perfectly good worksheet function called SUMIFS that should be able to complete the task just fine.

For future reference, please remember to post any code with code tags, the easiest way is to click the </> icon on the reply box toolbar the paste your code to the popup window.
I don't think that it is an official rule here, but it is preferred by most.

Thanks for the heads up Jason, I will definitely post codes with the correct tags in the future. I would love to use a simple formula, but I don't think it's possible as the matches are not exact (i.e. search for "BBC" in "Delivery of products 01/02 BBC 30000ex.) and the coloumns are really long.

 

[jasonb75]

To sum values in column B where the criteria of "BBC" is found as a partial match in column A, try

=SUMIF(A:A,B:B,"*BBC*")

If "BBC" is in C2, then it would be

=SUMIF(A:A,B:B,"*"&C2&"*")

There are many variations depending on the exact requirement.

 

[ElizabethC]

Thanks for your help Jason! I've tried that formula but to no avail: when I calculate the formula step-by-step I see that the "*"&C4&"*" turns into #Name?&#Name? (see photos attached). I've done ever so much googling and the only thing I can think of is that perhaps because I work in Russia and hence use the Russian version of excel, the method of displaying a wildcard is different...though I have googled in Russian (poor as my Russian is) and can't seem to find any info.

 

[Fluff]

It looks as though the quotes are wrong, they should be " rather than the slanted ones in your image

 

[ElizabethC]

I've even tried with VBA as I know that there shouldn't be a difference between the Russian and English variations, but I recieved a syntax error.

Sub Excel_SUMIF_Function()
'declare a variable
Dim ws As Worksheet, w1 As Worksheet
Set ws = Worksheets("List1")
Set w1 = Worksheets("ALL JOBS JANUARY")
'apply the Excel SUMIF function
w1.Range("D4") = Application.WorksheetFunction.SumIf(ws.Range("B2:B400"), ("" * "&w1.Range("C4")&" * ""), ws.Range("D2:D400"))

End Sub

 

[Fluff]

It should be

w1.Range("D4") = Application.WorksheetFunction.SumIf(ws.Range("B2:B400"), ("*" & w1.Range("C4") & "*"), ws.Range("D2:D400"))

 

[ElizabethC]

It looks as though the quotes are wrong, they should be " rather than the slanted ones in your image

You've saved me Fluff! You're absoloutely right: for some reason the quotation marks on the russian keyboard don't work with excel. I copied yopurs and now the formula works pefrectly. Thank you very much!!!

 

[Fluff]

You're welcome & thanks for the feedback