- Aug 14, 2018
- Office Version
I have some code that needs to check the rows in a worksheet and ask if this row is the correct row. This is a yes/no message box. I have a sheet with 4 entries having the same request number and date currently. The code will cycle through each visible row and ask the question but the trouble is that if doesn't matter what row you press yes for, only the first row in the list has the code applied it, not the one you select yes for. Could someone help me with the code that will apply the FoundRightJob code to the row that yes is selected for please?
For Each ws In wb2.Worksheets If ws.Name <> "Cancellations" And ws.Name <> "Totals" And ws.Name <> "Sheet2" Then With ws.[A3].CurrentRegion 'On Error Resume Next 'Autofilter the late cancel date enter in B37 with dates in column 1 .AutoFilter 1, LCDt 'Autofilter the late cancel request number with request numbers in column 3 .AutoFilter 3, LCReq 'Check to see if the date cell, column A, for a job has anything in it. If it doesn't, turn the autofilter off and skip to the next sheet. If ws.[A3].Cells.Offset(1, 0) = "" Then .AutoFilter SheetCounter = SheetCounter + 1 'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know If SheetCounter = 12 Then MsgBox "A job with the date and request number entered does not exist" End If GoTo SkipNextSheet End If 'Check the count Autofilter AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count 'If value less than 2, only the heading is visible so skip to the next sheet. If AutoFilterCounter < 2 Then .AutoFilter 'Add 1 to a sheet counter SheetCounter = SheetCounter + 1 'If SheetCounter = 12, none of the 12 monthly sheets have the entered date and request number so let the user know If SheetCounter = 12 Then MsgBox "A job with the date and request number entered does not exist" End If GoTo SkipNextSheet End If 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 row As Range Dim rws&: rws = Range("A4:A" & Cells(Rows.Count, 1).End(3).row).SpecialCells(xlCellTypeVisible).Count If rws > 1 Then 'If ws.Range("A4", ws.Range("A4").End(xlDown)) > 1 Then Dim answer As Integer For Each row In rng answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price") If answer = vbYes Then GoTo FoundRightJob 'If answer = vbNo Next row End If FoundRightJob: