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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
660
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,038
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
660
Office Version
2016
Platform
MacOS
That worked great! Thanks for the help on that.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,545
Messages
5,511,951
Members
408,871
Latest member
Usman21

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top