Run-time Error '6': Overflow

[Guinaba]

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

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

 

[mohadin]

Hi
Will you try

Dim ArrMTHAVG As Variant
Dim ArrSalesQty As Variant
Dim ArrSeasIndex As Variant

 

[Guinaba]

Hi
Will you try

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]

Ok
And good luck
Be happy