Run-time Error '6': Overflow

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
96
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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
96
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,128,177
Messages
5,629,182
Members
416,370
Latest member
Lgathana

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