malleshg24
07-10-2018, 11:11 PM
Hi Team, :help
I want to search multiple text in a visible cell by using sumproduct formula, if found then in immediate columns next cell I want "DATA Found text.
else leave blank. My attempted code. I have huge data above (1.5 lacs row) . plz assist for below code or any other option ok for it. Thanks in advance !!!
Sub Test()
Dim lr As Long
Dim c As Range
lr = Range("a1").CurrentRegion.Rows.Count
With Sheet1
If .AutoFilterMode = True Then .AutoFilterMode = False
.Range("a1").CurrentRegion.AutoFilter field:=2, Criteria1:=""
If Application.WorksheetFunction.Subtotal(3, .Range("B2:B" & lr).SpecialCells(xlCellTypeVisible)) > 1 Then
For Each c In .Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
if c.value = SUMPRODUCT(--ISNUMBER(SEARCH({""red"",""blue"",""green""},c.value)))>0 then
c.Offset(, 1).Value = found
Next c
End If
End With
End Sub
Regards,
Mallesh
I want to search multiple text in a visible cell by using sumproduct formula, if found then in immediate columns next cell I want "DATA Found text.
else leave blank. My attempted code. I have huge data above (1.5 lacs row) . plz assist for below code or any other option ok for it. Thanks in advance !!!
Sub Test()
Dim lr As Long
Dim c As Range
lr = Range("a1").CurrentRegion.Rows.Count
With Sheet1
If .AutoFilterMode = True Then .AutoFilterMode = False
.Range("a1").CurrentRegion.AutoFilter field:=2, Criteria1:=""
If Application.WorksheetFunction.Subtotal(3, .Range("B2:B" & lr).SpecialCells(xlCellTypeVisible)) > 1 Then
For Each c In .Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
if c.value = SUMPRODUCT(--ISNUMBER(SEARCH({""red"",""blue"",""green""},c.value)))>0 then
c.Offset(, 1).Value = found
Next c
End If
End With
End Sub
Regards,
Mallesh