Solved: Look for multiple texts in the Find box vba

[Shazam]

Hi Everyone,


I need a little modifying. I would like the Find method to look for multiple texts.

I have this line below.

Set i = .Find("SINTO (C&D #4) Total", LookIn:=xlValues, lookat:=xlWhole)


I tried to modify into this:

Set i = .Find("COPE ( #2 ) Total,COPE ( #3 ) Total, (C&D #4) Total", LookIn:=xlValues, lookat:=xlWhole)


But it does not work.


Here is the entire code I'm working with.




With ActiveSheet.Columns("I:I")
Set i = .Find("DRAG ( #2 ) Total,DRAG ( #3 ) Total,(C&D #4) Total", LookIn:=xlValues, lookat:=xlWhole)
If Not i Is Nothing Then
firstAddress = i.Address
Do
i.Offset(0, 6).FormulaArray = "=SUM(IF(FREQUENCY(IF(R3C9:RC9=R[-1]C9,IF(R3C6:RC6<>""""," & _
"MATCH(R3C6:RC6&R3C6:RC6,R3C6:RC6&R3C6:RC6,0))),ROW(R3C6:RC6)-ROW(R3C1)+1)>0," & _
"R3C15:R[-1]C15))"

Set i = .FindNext(i)
Loop While Not i Is Nothing And i.Address <> firstAddress
End If
End With

[Bob Phillips]

Can't see that working Shaz. I would think you would need to look for one, and when not found, look for the next in the list, and so on.

[Jacob Hilderbrand]

Does the text exist exactly as you have it? Including spaces? I notice that you are missing a space after the comma after "Drag ( #2 ) Total," which could be an issue.

Can you post the workbook as well and we can see exactly what is in the cells and what you are searching for?

[Shazam]

Thank you for replying.

Here is an example workbook below.


Hi DRJ

Yes the text are exactly how I have it.



Note: My data fluctuates daily.

[Jacob Hilderbrand]

The cell has "(C&D #4) Total" in it, not the whole string you are searching for. Are you trying to search for each separate part of that string and put the formula in the cells for each?

If so, then as xld suggested, you will need to search for each, one at a time.



Set i = .Find("COPE ( #2 ) Total", LookIn:=xlValues, lookat:=xlWhole)


Set i = .Find("COPE ( #3 ) Total", LookIn:=xlValues, lookat:=xlWhole)


Set i = .Find("(C&D #4) Total", LookIn:=xlValues, lookat:=xlWhole)




So search, then loop, then search again, then loop again etc.

I would suggest something like this:


Sub Macro1()

Call Macro2("DRAG ( #2 ) Total")
Call Macro2("DRAG ( #3 ) Total")
Call Macro2("(C&D #4) Total")

End Sub

Sub Macro2(LookFor As String)

With ActiveSheet.Columns("I:I")
Set i = .Find("LookFor", LookIn:=xlValues, lookat:=xlWhole)
If Not i Is Nothing Then
firstAddress = i.Address
Do
i.Offset(0, 6).FormulaArray = "=SUM(IF(FREQUENCY(IF(R3C9:RC9=R[-1]C9,IF(R3C6:RC6<>""""," & _
"MATCH(R3C6:RC6&R3C6:RC6,R3C6:RC6&R3C6:RC6,0))),ROW(R3C6:RC6)-ROW(R3C1)+1)>0," & _
"R3C15:R[-1]C15))"

Set i = .FindNext(i)
Loop While Not i Is Nothing And i.Address <> firstAddress
End If
End With

End Sub

[Shazam]

So search, then loop, then search again, then loop again etc.

I would suggest something like this:


Sub Macro1()

Call Macro2("DRAG ( #2 ) Total")
Call Macro2("DRAG ( #3 ) Total")
Call Macro2("(C&D #4) Total")

End Sub

Sub Macro2(LookFor As String)

With ActiveSheet.Columns("I:I")
Set i = .Find("LookFor", LookIn:=xlValues, lookat:=xlWhole)
If Not i Is Nothing Then
firstAddress = i.Address
Do
i.Offset(0, 6).FormulaArray = "=SUM(IF(FREQUENCY(IF(R3C9:RC9=R[-1]C9,IF(R3C6:RC6<>""""," & _
"MATCH(R3C6:RC6&R3C6:RC6,R3C6:RC6&R3C6:RC6,0))),ROW(R3C6:RC6)-ROW(R3C1)+1)>0," & _
"R3C15:R[-1]C15))"

Set i = .FindNext(i)
Loop While Not i Is Nothing And i.Address <> firstAddress
End If
End With

End Sub




Perfect thanks DRJ!!!