Consulting

Results 1 to 4 of 4

Thread: VBA Help - on Visible cells

  1. #1

    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,111
    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 - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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
    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 Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,111
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    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
  •