troubleshooting Question

Overflow error in Excel

Avatar of Mike French
Mike FrenchFlag for United States of America asked on
Microsoft OfficeMicrosoft Applications
7 Comments1 Solution333 ViewsLast Modified:
I am getting an overflow error with this code. Please help me understand why. When I comment out the variables j, k, & z I don't get the error. The data is wrong but i don't get the error. What is causing this?

Public Sub CalculateYTDCustomerSalesAndMargins()

Dim UniqueCustomers, Output, Claimbacks As Variant
Dim i, c, j, k, z As Double

UniqueCustomers = Range("UniqueCustomers")
Claimbacks = Range("ClaimBacks")

ReDim Output(1 To UBound(UniqueCustomers), 1 To 5)

j = 0   'Sales variable
k = 0   'Cost of sales variable
z = 0   'Claim backs variable

For c = 1 To UBound(UniqueCustomers)

    For i = 1 To UBound(Claimbacks)
    
    If UniqueCustomers(c, 2) = Claimbacks(i, 5) Then
    
    j = j + Claimbacks(i, 11) * Claimbacks(i, 10)
    k = k + Claimbacks(i, 12) * Claimbacks(i, 10)
    z = z + Claimbacks(i, 22)
    
    Output(c, 1) = j
    Output(c, 2) = k
    Output(c, 3) = 1 - k / j
    Output(c, 4) = z
    Output(c, 5) = 1 - (k - z) / j
     
    End If
    
    Next i

    
j = 0   'Sales variable
k = 0   'Cost of sales variable
z = 0   'Claim backs variable

Next c

Range("E2", Range("I" & UBound(UniqueCustomers) + 1)) = Output


End Sub
ASKER CERTIFIED SOLUTION
GrahamSkan
Retired
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros