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

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
693
Office Version
  1. 2016
Platform
  1. 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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top