Loop through cells and display a message if a value is not found

Question

I have a macro that loops through cells of one sheet, looks for that value in another sheet, and then highlights the row if they match. I'd like to add a message box that would pop up if a matching value is not found. I know this is a simple problem, but I'm having trouble figuring out in which loop to put my booleans.


Sub MarkXfer_noX()

Dim rng As Range
Dim rng2 As Range
Set rng = Worksheets("Transferred Routings").UsedRange
Dim i As Integer
Dim j As Integer
Dim ProdCI As String
Dim found As Boolean
Dim intRowCount As Integer


intRowCount = Sheets("Transferred Routings").UsedRange.Rows.count

For i = 2 To intRowCount

        If rng.Cells(i, 1) <> "" Then ProdCI = rng.Cells(i, 1) 'get the ProdCI number from column A if not blank
        Worksheets("All_ProCI").Activate 'activate main page
        Set rng2 = Worksheets("All_ProCI").UsedRange 'select a range on the main page

            For j = 2 To rng2.Rows.count 'from row 2 to the end
                If rng2.Cells(j, 2) = ProdCI Then 'if the ProdCI in column B matches the one we picked,
                    Call FillCell(j) 'call a sub in a different module and give it our current row
                    found = True
                Else
                    found = False
                End If
            Next

   Next

   If found = False Then
   MsgBox (ProdCI & " not found") 'Display a message if one of the items wasn't found on the main page. Currently has an error where the last one in the list always pops up.
   Else
   End If

End Sub

Right now it always shows a msgbox with the last value in the range no matter what.

Look here: https://stackoverflow.com/questions/56854086/how-to-highlight-duplicates-in-column-that-are-not-blanks/56854573#56854573 This is for a single column but the concept is the same. — Warcupine, Jul 08 '19 at 15:12
(1) You need to exit your loop once found; (2) using the `Find` method would be more efficient than looping. — SJR, Jul 08 '19 at 15:14
Apart from @SJR options, (3) you could do this with `Index + Match` formula (in your VBA code if you really wanted). Excel's C++ code will outperform any macro that you ever write.. just a tip — Zac, Jul 08 '19 at 15:52
[THIS](http://www.siddharthrout.com/index.php/2018/01/11/find-and-findnext-in-excel-vba/) will get you started — Siddharth Rout, Jul 08 '19 at 15:52

score 0 · Answer 1 · answered Jul 08 '19 at 17:59

Thanks all, here is the updated working code using the Find function

Sub MarkXfer_Find()

'Re-tooled to use the .Find function instead of looping through each
Dim rng As Range
Dim rng2 As Range
Set rng = Worksheets("Transferred Routings").UsedRange
Dim i As Integer
Dim ProdCI As String
Dim intRowCount As Integer
Dim intRowCount2 As Integer
Dim aCell As Range

intRowCount = Sheets("Transferred Routings").UsedRange.Rows.count

For i = 2 To intRowCount

        If rng.Cells(i, 1) <> "" Then ProdCI = rng.Cells(i, 1) 'get the ProdCI number from column A if not blank
        Worksheets("All_ProCI").Activate 'activate main page
        Set rng2 = Worksheets("All_ProCI").UsedRange 'select a range on the main page
        intRowCount2 = Worksheets("All_ProCI").UsedRange.Rows.count

        'use the Find function to put a value in aCell
        Set aCell = rng2.Range("B1:B" & intRowCount2).Find(What:=ProdCI, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            'MsgBox ProdCI & " found"
            Call FillCell(aCell.row)

        Else 'If aCell is blank display msgbox
        MsgBox "ProdCI """ & ProdCI & """ not found"
        End If

Next


End Sub

Loop through cells and display a message if a value is not found

1 Answers1