- Aug 14, 2018
- Office Version
I have a procedure that cycles through some visible rows, asking if is this row is the right row. The sub works but if you press no each for each question, I want to display a message msgbox. Could someone show me what is wrong with my code please?
Dim EndMsgCounter As Integer Dim LastRow As Long LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row Dim rng As Range: Set rng = ws.Range("A4:A" & LastRow) Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).Row).SpecialCells(xlCellTypeVisible).Count 'if this range is greater than 1, ask the below question, else continue If rws > 1 Then 'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then Dim answer As Integer Dim RowNumber As Long Dim RowLine As Range Application.ScreenUpdating = True For Each RowLine In rng.SpecialCells(xlCellTypeVisible) ws.Activate EndMsgCounter = 1 RowLine.EntireRow.Interior.ColorIndex = 6 answer = MsgBox("Is this the job you want to cancel?", vbQuestion + vbYesNo + vbDefaultButton2, "Cancel Job") RowLine.EntireRow.Interior.ColorIndex = 0 If answer = vbYes Then 'Copy and paste the identified row to the cancellations sheet RowLine.EntireRow.Copy sht.Range("A" & Rows.Count).End(xlUp).Offset(1) RowLine.EntireRow.Delete 'I had to include a -3 in here to account for the 3 rows above the data that don't have data in them RowNumber = RowLine.Row - 3 GoTo FoundRightJob End If 'If answer = vbNo If rws = EndMsgCounter Then 'Display a message box if no is pressed each time MsgBox "No jobs have been cancelled" Exit Sub End If EndMsgCounter = EndMsgCounter + 1 Next RowLine End If FoundRightJob: