dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I am getting the overflow error and I can't work out why.
This is my code:
I have a sheet for every month of the year. They are all identical with the following format
This is where I enter data about jobs that have a late cancel
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:
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
Can someone help me please?
This is my code:
VBA 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:
VBA 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?