dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I have some code in my spreadsheet that needs to cycle through each item in a filtered list. Typically, there will be no more than 3 with the filtered list starting in A4
The last section of code is the bit I have recently added. I have 2 entries in one of my sheets that match the autofilter for LCDt and LCReq so I would imagine that I would get the message box twice but it asks me 59 times. Can someone tell me what I have done wrong please?
VBA Code:
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 rng As Range, x As Range, answer As Integer
Set rng = ws.Range("A4:O7").SpecialCells(xlCellTypeVisible)
For Each x In rng
answer = MsgBox("Is this the job you want to apply the late cancel price too?", vbQuestion + vbYesNo + vbDefaultButton2, "Late Cancel Price")
Next x
The last section of code is the bit I have recently added. I have 2 entries in one of my sheets that match the autofilter for LCDt and LCReq so I would imagine that I would get the message box twice but it asks me 59 times. Can someone tell me what I have done wrong please?