PDA

View Full Version : VBA Help - on Visible cells



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

p45cal
07-11-2018, 04:42 AM
Is this what you're trying to do?:
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

malleshg24
07-11-2018, 09:29 PM
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

p45cal
07-12-2018, 02:42 AM
see:
https://exceljet.net/formula/double-quotes-inside-a-formula
and
https://www.ozgrid.com/forum/forum/help-forums/excel-general/86667-use-of-variable-in-formula-inserted-by-vba-code