Overflow

[dpaton05]

I am getting the overflow error and I can't work out why.

This is my code:

Sub LateCancel()


        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Dim Serv As String, Month As String, Service As String, LCPrice As String, AutoFilterCounter As Long
        Set wb2 = ThisWorkbook
        QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")

        'values on totals sheet that the user is looking for
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        Dim LCDt As String: LCDt = sh.Cells(34, 2).Value
        
        WbPath = ThisWorkbook.Path
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Application.ScreenUpdating = False
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        'If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
    
        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 B34 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                'Add the service to a varaible
                                'Service = .Areas(1).Cells(2, 5).Value
                                
                                
                                
                                'Add code for filter to check number of
                                AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count
                                    'If value is 1, only the heading is visible so skip to the next sheet.
                                If AutoFilterCounter < 2 Then GoTo SkipNextSheet
                                    
                                    
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        'Check if there is a service entered in column 5 of the filtered job
                                        If .Areas(1).Cells(1, 5).Value = "" Then
                                            MsgBox "There is a job in the " & ws.Name & " sheet that matches the date and request number that does not have a service number. " _
                                            & "Please add a service type to this job before continuing."
                                            Exit Sub
                                        End If
                                        Service = .Areas(1).Cells(1, 5).Value
                                    End With
                                
                                    'Copy data fom the job back to a calculator on the data sheet (this is the code name for sheet2) to calulcate the price again.
                                    With Data
                                        .Cells(30, 1) = LCDt
                                        .Cells(30, 2) = Service
                                        'A late cancel will be charged at a 3 hour duration
                                        .Cells(30, 5) = 3
                                        'A late cancel will be charged for 1 staff member attending
                                        .Cells(30, 6) = 1
                                    End With
                                
                                    LCPrice = Data.Cells(30, 8).Value
                                
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        .Areas(1).Cells(1, 8).Value = LCPrice
                                        .Areas(1).Cells(1, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                        .Areas(1).Cells(1, 10).Formula = "=RC[-1]+RC[-2]"
                                    End With
                                
                              
                                .AutoFilter
                        End With
                End If

SkipNextSheet:
        Next ws
'sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True


End Sub

I have a sheet for every month of the year. They are all identical with the following format

CSS Work Allocation Sheet.40.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Date	Purchase order #	Req #	Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST	Allocated to	Date report received	Date report sent	Allocated by	Report sent by
5
September

This is where I enter data about jobs that have a late cancel

CSS Work Allocation Sheet.40.xlsm
	B	C	D	E
29	Late Cancel
30	Enter a request number first, followed by the date
31	Request number
32	2
33	Date
34	15/08/2020
35
36
Totals

I did some research and overflow looks like a value is trying to be assigned to a variable that will not fit in the variable. The code halts on this line of code:

AutoFilterCounter = .Columns(1).SpecialCells(xlCellTypeVisible).Count

This happens on the october sheet but it doesn't seem to matter what sheet is in the 6th position, it will halt on that sheet.

From left to right, my sheets are"
July, Sheet2, Totals, August, September, October, November, December.......June, Cancellations


I am not sure if you need it but here is the calculator I was talking about

CSS Work Allocation Sheet.40.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
29	Date	Service	Unit Price	Day rate	Hours	Staff Req.	Kms Travelled	Price ex. GST	Rate	Transport $	MaxPay	Column3	Activities	Column1	Column2	Column22	Column23
30	15/08/2020		#N/A	Sat	3	1		#N/A	#N/A	0.00	#N/A
Sheet2

Cell Formulas
Range	Formula
C30	C30	=IF([@Service]="Activities",[@Activities],INDEX(Service_Types,MATCH([@Service],Sheet2!$A$5:$A$12,0),MATCH([@[Day rate]],Sheet2!$A$5:$E$5,0)))
D30	D30	=IF(A30="","",IF(COUNTIF(Sheet2!$G$87:$DO$97,A30),"Public_holiday",IF(WEEKDAY(A30)=1,"Sun",IF(WEEKDAY(A30)=7,"Sat","Business_day_rate"))))
H30	H30	=IF([@Service]="Activities",ROUNDDOWN([@Activities]+[@[Transport $]],2),IF([@Service]="Carer Respite",[@[Staff Req.]]*[@Rate],ROUNDDOWN(((IF(OR(ISBLANK(A11),ISBLANK(D11),ISBLANK(B11)),0,[@[Transport $]]+[@MaxPay]))*[@[Staff Req.]]),2)))
I30	I30	=INDEX(Sheet2!$A$5:$E$12,MATCH([Service],Sheet2!$A$5:$A$12,0),MATCH([Day rate],Sheet2!$A$5:$E$5,0))
J30	J30	=([@[Kms Travelled]]*1.22)
K30	K30	=[Rate]*[Hours]

Named Ranges
Name	Refers To	Cells
Service_Types	=Sheet2!$A$5:$E$12	I30, C30

Can someone help me please?

 

[Michael M]

Worth a try.....turn off the autofilter before going to the next sheet

 

[dpaton05]

I thought that it was turned off as I have a .autofilter at the end.

 

[Michael M]

No, I meant here...

If AutoFilterCounter < 2 Then 
.Autofilter
GoTo SkipNextSheet

 

[dpaton05]

I am not sure what I have added that already in the code I have now. I got it to work with this code:

Sub LateCancel()


        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, QT As String, wb2 As Workbook, WbPath As String, QTPath As String
        Dim Serv As String, Month As String, Service As String, LCPrice As String, AutoFilterCounter As Long
        Set wb2 = ThisWorkbook
        QT = "CSS_quoting_tool_29.5.xlsm"
        Set sh = wb2.Worksheets("Totals")

        'values on totals sheet that the user is looking for
        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        Dim LCDt As String: LCDt = sh.Cells(34, 2).Value
        
        WbPath = ThisWorkbook.Path
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Application.ScreenUpdating = False
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        'If Not isFileOpen(QT) Then Workbooks.Open QTPath & "\" & QT
    
        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 B34 with dates in column 1
                                .AutoFilter 1, LCDt
                                'Autofilter the late cancel request number with request numbers in column 3
                                .AutoFilter 3, LCReq
                                'Add the service to a varaible
                                'Service = .Areas(1).Cells(2, 5).Value
                                
                                '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
                                    GoTo SkipNextSheet
                                End If
                                
                                'Add code for filter to check number of
                                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
                                    GoTo SkipNextSheet
                                End If
                                    
                                    
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        'Check if there is a service entered in column 5 of the filtered job.
                                        If .Areas(1).Cells(1, 5).Value = "" Then
                                            'Display a messagebox with a message and the sheet that has the missing service.
                                            MsgBox "There is a job in the " & ws.Name & " sheet that matches the date and request number but does not have a service number. " _
                                            & "Please add a service type to this job before continuing."
                                            Exit Sub
                                        End If
                                        'If the service column, (5), has a value, store the service in the service variable.
                                        Service = .Areas(1).Cells(1, 5).Value
                                    End With
                                
                                    'Copy data fom the job back to a calculator on the data sheet (this is the code name for sheet2) to calulcate the price again.
                                    With Data
                                        .Cells(30, 1) = LCDt
                                        .Cells(30, 2) = Service
                                        'A late cancel will be charged at a 3 hour duration
                                        .Cells(30, 5) = 3
                                        'A late cancel will be charged for 1 staff member attending
                                        .Cells(30, 6) = 1
                                    End With
                                
                                    LCPrice = Data.Cells(30, 8).Value
                                
                                    With Application.Intersect(.SpecialCells(xlCellTypeVisible), .Offset(1, 0))
                                        .Areas(1).Cells(1, 8).Value = LCPrice
                                        .Areas(1).Cells(1, 9).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                                        .Areas(1).Cells(1, 10).Formula = "=RC[-1]+RC[-2]"
                                    End With
                                
                              
                                .AutoFilter
                        End With
                End If

SkipNextSheet:
        Next ws
'sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True


End Sub

I was getting an error if there was blank cells after the autofilter was done so I thought that I could check to see if there was a date entered in the first cell. If it didn't find a date, I thought that would be a pretty good indication of there not being anything in the sheet, so skip to the next sheet.

Why would a blank cell of returned that error after doing the autofilter?

 

[Michael M]

Are the blank cells Truly BLANK ??