PDA

View Full Version : Reveal all matches with FIND function



ElizabethC
03-06-2020, 06:28 AM
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

NoSparks
03-06-2020, 07:52 AM
deleted due to cross posting

大灰狼1976
03-10-2020, 02:09 AM
Untested.

Sub FindVLookup()
Dim rngSearch As Range, rngOrdernames As Range, rngFound As Range, rngOrders As Range, Cnt
Set rngSearch = Sheets("List1").Range("B1:B400")
Set rngOrdernames = Sheets("ALL JOBS JANUARY").Range("C3:C300")
For Each rngOrders In rngOrdernames
Cnt = Application.SumIf(rngSearch, rngOrders, rngSearch.Offset(, 1))
If Cnt Then rngOrders.Offset(0, 1) = Cnt
Next
End Sub