VBA Help - Search Column For String, Do Something, Repeat Search

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
652
Office Version
2016
Platform
MacOS
Hello all I am working with a code that does multiple Loops thru Sheets based on a Matrix, the Matrix contains a Product ID that will need to be searched on specific sheets. For this search, there will always be at least one value found on the sheet the code navigates too. Search column will always be Column C on each sheet.

So what I need is to have the .Find function look at Range("C7:C" & LastRow) and find the Product ID, once found Get the Cell.Row and then Do Something, once that something is done, continue the search in that column, if the Product ID is not found then stop the search.

Finding the first product ID in this code is the easy part, I am struggling with how to write code to have it essentially loop the search and not look at the first value the search found. Any help is appreciated.

*FYI - I am not able to use a Scripting Dictionary in the event anyone was going to propose that idea.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,912
Office Version
2019
Platform
Windows
Hi,
Range.FindNext method should do what you require

taken from VBA helpfile

Code:
With Worksheets(1).Range("a1:a500")
     Set c = .Find(2, lookin:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        If c is Nothing Then
            GoTo DoneFinding
        End If
        Loop While c.Address <> firstAddress
      End If
      DoneFinding:
End With
Adjust as required


Dave
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
Code:
Sub Main()
    Dim ws As Worksheet, r As Range, c As Range, d As Long, ID, s As Sheets, a
    
    ID = 12345
    Set s = Worksheets(Split("Sheet1,Sheet2,Sheet3", ","))
    'Set s = Sheets  'All sheets
    
    For Each ws In s
        Set r = ws.Range("C7", ws.Cells(Rows.Count, "C").End(xlUp))
        If (r.Row >= 7) Then
            d = d + WorksheetFunction.CountIf(r, "=" & ID)
        End If
    Next ws
    
    ReDim a(1 To d)
    d = 0
    For Each ws In s
        Set r = ws.Range("C7", ws.Cells(Rows.Count, "C").End(xlUp))
        If (r.Row >= 7) Then
            For Each c In r
                If (c = ID) Then
                    d = d + 1
                    a(d) = c.Address(external:=True)
                End If
            Next c
        End If
    Next ws

    MsgBox Join(a, vbLf)
End Sub
 

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
652
Office Version
2016
Platform
MacOS
That worked great! Thanks for the help on that.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top