With F1 value, look at A, if match or partial match return B
you don't need SUMPRODUCT.
That is enough to get the result:
=IF(ISNUMBER(FIND($F$1,A2)),B2,"")
In the other formulas you have a placeholder (asterix) also in front of
the substring. If the substring begins with the first character you get
an error.
FIND works in all cases.
Yes, works very well. 10,000+ rows,,, in a blink.
Sub Pn_Col_D()
Dim LRow As Long
LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Range("G2", Range("G2").End(xlDown)).ClearContents
With Range("G2").Resize(LRow, 1)
.Formula = "=IF(ISNUMBER(FIND($F$1,A2)),B2,"""")"
'.Formula = "=IF(SUMPRODUCT(ISNUMBER(FIND($F$1,A2))*1)=1,B2,"" "")"
.Value = .Value
End With
End Sub
Thanks again.
Howard
|