Consulting

Results 1 to 4 of 4

Thread: VBA Help - on Visible cells

  1. #1
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location

    VBA Help - on Visible cells

    Hi Team,


    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
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    Thanks it worked,

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

    Regards
    Mallesh

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •