VBA Help - on Visible cells

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

Is this what you're trying to do?:

Code:

---

Sub Test()
Dim lr As Long
Dim c As Range, myRange As Range

With Sheet1
  If .AutoFilterMode = True Then .AutoFilterMode = False
  lr = Cells(Rows.Count, "A").End(xlUp).Row
  .Range("a1:b" & lr).AutoFilter field:=2, Criteria1:=""
  On Error Resume Next
  Set myRange = .Range("a2:a" & lr).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
  If Not myRange Is Nothing Then
    For Each c In myRange.Cells
      If Evaluate("SUMPRODUCT(--ISNUMBER(SEARCH({""red"",""blue"",""green""},""" & c.Value & """)))") > 0 Then c.Offset(, 1).Value = "found"
    Next c
  End If
End With
End Sub

---

Thanks it worked,:clap:

One small question , can you tell reg :=> """& c.value &""" you have used 3 double quotation here .how it works

Regards
Mallesh

see:
https://exceljet.net/formula/double-...side-a-formula
and
https://www.ozgrid.com/forum/forum/h...ed-by-vba-code