Macro to goes through each row to find value, if found create a message box

[abiggale]

Hi everyone,

I have created a macro that should go through each row of a worksheet and look at two columns for specific values. If these values are in a row, a message box should appear asking to confirm that these details are true. If you click yes, the macro will continue, if you press no, the workbook will close without saving. I am getting an error when I run this macro and I am not sure why, can someone help?

      Dim x As String     
      Dim y As String
      Dim i As Integer
      Dim found As Boolean
      Dim Message As String


      Sheet1.Select
      Range("A1").Select
      x = "CIBC"
      y = "FRN"
      found = False
      i = 1
      Do Until IsEmpty(ActiveCell)
         If Cells(i, 4).Value = x And Cells(i, 6).Value = y Then
            found = True
            If found = True Then
                Message = MsgBox("Please confirm that the CUID for broker: " & Cells(i, 4) & " and product: " & Cells(i, 21) & " is " & Cells(i, 19), vbYesNo, "CIBC FRN")
                If Message = vbNo Then
                    ActiveWorkbook.Saved = True
                    ActiveWorkbook.Close
                    Else
                End If
            End If
            End If
         i = i + 1
      Loop

 

[Caribeiro77]

In what line is the error showing?
I ran the code step by step i did everything is was supposed to do...

 

[Caribeiro77]

In what line is the error showing?
I ran the code step by step i did everything is was supposed to do...

Sorry, didn't read till the end.....is when you click the no button....in did it's not saving....let me run it again..

 

[Caribeiro77]

What about:
(Try the code on a copy of your original file)

 Dim x As String
      Dim y As String
      Dim i As Integer
      Dim found As Boolean
      Dim Message As String




      Sheet1.Select
      Range("A1").Select
      x = "CIBC"
      y = "FRN"
      found = False
      i = 1
      Do Until IsEmpty(ActiveCell)
         If Cells(i, 4).Value = x And Cells(i, 6).Value = y Then
            found = True
            If found = True Then
                Message = MsgBox("Please confirm that the CUID for broker: " & Cells(i, 4) & " and product: " & Cells(i, 21) & " is " & Cells(i, 19), vbYesNo, "CIBC FRN")
                If Message = vbNo Then
                   ThisWorkbook.Save
                   ThisWorkbook.Close
                    Else
                End If

End If
            End If
         i = i + 1
      Loop
End Sub

 

[abiggale]

What about:
(Try the code on a copy of your original file)

 Dim x As String
      Dim y As String
      Dim i As Integer
      Dim found As Boolean
      Dim Message As String




      Sheet1.Select
      Range("A1").Select
      x = "CIBC"
      y = "FRN"
      found = False
      i = 1
      Do Until IsEmpty(ActiveCell)
         If Cells(i, 4).Value = x And Cells(i, 6).Value = y Then
            found = True
            If found = True Then
                Message = MsgBox("Please confirm that the CUID for broker: " & Cells(i, 4) & " and product: " & Cells(i, 21) & " is " & Cells(i, 19), vbYesNo, "CIBC FRN")
                If Message = vbNo Then
                   ThisWorkbook.Save
                   ThisWorkbook.Close
                    Else
                End If

End If
            End If
         i = i + 1
      Loop
End Sub

Hi,

The macro is supposed to close the workbook without saving. If you run the macro step by step it works but at the end it gives me an error that says "overflow" I am not sure what that means so I'm having trouble fixing it. Can you help?

 

[abiggale]

I got it to work, The error was because the loop continued until "i" maxed out. thanks for your help!