Run-time Error '6': Overflow

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
92
Hi guys,

Not sure what I am missing here, I keep getting the error: Run-time Error '6': Overflow . I’ve tried to use the function CLng but didn’t worked.
I am looping through Excel table (not range) where I have to calculate the monthly average:

'Calculating MTH AVG

For r = 2 To MthAvg.Rows.Count

WS2.Range("P" & r).FormulaR1C1 = "=AVERAGEIFS(C[-4],C[-11],[@[Mth/Year]],C[-6],[@ItemID])"

Next r

After looping through the col. SalesQty and using averageifs formula I am populating 3 arrays:

Dim ArrAVG() As Variant ‘Had tried as long but didn't worked

Dim ArrSalesQty() As Variant

Dim ArrSeasIndex() As Variant

And I do another loop using the arrays (arrSalesQty and ArrMTHAVG) to calculate ArrSeasIndex

'Calculating Seasonal Index

For i = LBound(ArrSeasIndex) To UBound(ArrSeasIndex)

ArrSeasIndex(i, 1) = CLng(ArrSalesQty(i, 1)) / CLng(ArrMTHAVG(i, 1))

Next i

VBA Code:
Sub Promo_Finder()



With Application 'Disable screen update

.Calculation = xlCalculationManual

.EnableEvents = False

.ScreenUpdating = False

End With



Dim WS2 As Worksheet

Set WS2 = ThisWorkbook.Worksheets(2)

Dim LastRow As Long

LastRow = WS2.Cells(Rows.Count, "j").End(xlUp).Row

Dim r, i As Long

Dim ArrMTHAVG() As Variant

Dim ArrSalesQty() As Variant

Dim ArrSeasIndex() As Variant





'Declaring table

Dim ScanSales As ListObject

Set ScanSales = WS2.ListObjects("WW_Scan_Data_Chilled_ALL")



Dim MthAvg As Range

Set MthAvg = WS2.Range("P2:Q" & LastRow)



'Delete previous data

WS2.Range("P2:P" & LastRow).Clear

WS2.Range("Q2:Q" & LastRow).Clear



'Calculating MTH AVG

For r = 2 To MthAvg.Rows.Count

WS2.Range("P" & r).FormulaR1C1 = "=AVERAGEIFS(C[-4],C[-11],[@[Mth/Year]],C[-6],[@ItemID])"

Next r



ArrMTHAVG = WS2.Range("P2:P" & LastRow)

ArrSalesQty = WS2.Range("L2:L" & LastRow)

ArrSeasIndex = WS2.Range("Q2:Q" & LastRow)



'Calculating Seasonal Index

For i = LBound(ArrSeasIndex) To UBound(ArrSeasIndex)



ArrSeasIndex(i, 1) = CLng(ArrSalesQty(i, 1)) / CLng(ArrMTHAVG(i, 1))



Next i



With Application 'Enable screen update

.Calculation = xlCalculationAutomatic

.EnableEvents = True

.ScreenUpdating = True

End With
End Sub
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
Will you try
VBA Code:
Dim ArrMTHAVG As Variant
Dim ArrSalesQty As Variant
Dim ArrSeasIndex As Variant
 

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
92
Hi
Will you try
VBA Code:
Dim ArrMTHAVG As Variant
Dim ArrSalesQty As Variant
Dim ArrSeasIndex As Variant
Thanks @mohadin! I did that and also found the issue, there were some zero records as numerator in my formula, removing them from the calculation fixed the issue.
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Ok
And good luck
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,127,865
Messages
5,627,340
Members
416,242
Latest member
Kas O

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top