Check if cells in range are contained in another cell

[horizonflame]

Hi there

I am after some help with searching for a string. If the value in column L is found anywhere in cell A9 I want to enter the cell value (currently a formula). If the value is not found then change the cell to blank.

I've tried the below but it just makes all cells blank!

Your help is appreciated, thank you.

With Sheets("Data")
For each cell in .Range("L2:L" & LR1)
     If cell.value Like "*" & .Range("A9").value Then
          cell.value = cell.value
     Else
          cell.value = ""
Next cell

 

[Fluff]

Try it the otherway round

If .Range("A9").value Like "*" & cell.value & "*"  Then

 

[horizonflame]

Hi @Fluff, thank you but it now finds a match for all values even when there isn't!

 

[Fluff]

What do you have in L2 downwards & in A9?

 

[horizonflame]

L2-L5 for example is 3001548652, 3001548798, 3001548287, 3001549415

A9 is "3001548652, 3001549415"

 

[Fluff]

With that data it works for me, it matches L2 & L5, but not L3:L4
What is the formula in col L?

 

[horizonflame]

Hi @Fluff

My previous code puts this formula in Column L:

.Range("L2:L" & LR4).Formula = "=SUMPRODUCT(('Points'!$A$2:$A$" & LR3 & "=$K2)*('Points'!$C$2:$C$" & LR3 & "))"

Thank you.

 

[Fluff]

Putting that formula into L2:L5 & using the values from post#5 2 of them are converted to values & 2 are cleared.

 

[horizonflame]

Hi @Fluff I realised that where the SUMPRODUCT formula ended with a 0 result, it was matching with a 0 in the string creating a false positive.

Thanks for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.