Overflow Error

[khansen97]

I am getting an overflow error everytime I try to use a variable. This is the code I have:

Dim xunits As Long
Dim xrevenue As Long
Dim xholdback As Long
Dim lcleaninvoice As Long
Dim lfinholdback As Long

ActiveSheet.Cells(gdpcarline, 8).Value = xunits ActiveSheet.Cells(gdpcarline, 9).Value = xrevenue

This is where I am getting the overflow error:

lcleaninvoice = xrevenue / xunits

Any help?

 

[xlbo]

You are using a LONG data type - this stands for long INTEGER. Likelihood is that your division is producing non integer results - try using single or double - depending on how accurate you need to be

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[khansen97]

I found that it is coming up with an undefined result. Can you help me figure out a way to get around this? Can I put in an if statement?

thanks

 

[xlbo]

As I said before - 1st step - try changing your data types

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[khansen97]

I did. And I am still getting the overflow error. This is only happening when my variable returns an undefined result.

 

[Zathras]

What are the values in xrevenue and xunits?

From the code snippet provided, it looks like they are both zero. When they are both zero, you will get the Overflow error message every time.

perhaps you meant to say

    xunits = ActiveSheet.Cells(gdpcarline, 8).Value
  xrevenue = ActiveSheet.Cells(gdpcarline, 9).Value

instead of the other way around.

 

[xlbo]

What is coming up with an "undefined result" - the variable type or the result of the formula ??
If the latter, what are the values of the xrevenue and xunits variables ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[khansen97]

xrevenue and xunits are both zero. I put in an if statement to filter out when they are both zero. This solved my problem. I was just trying to figure out if there was another way around this.

 

[xlbo]

well you are ALWAYS gonna get an error if your divisor is 0 - why would you want anything else rather than an IF statement ???

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[Zathras]

You can use error trapping:

Replace the line:

   lcleaninvoice = xrevenue / xunits

with a function call like this:

   lcleaninvoice = CleanInvoice(xrevenue, xunits)

And code a function like this:

Function CleanInvoice(Revenue As Long, Units As Long) As Long
  On Error GoTo Skip
  CleanInvoice = Revenue / Units
  Exit Function
Skip:
  CleanInvoice = 0
End Function

But it is preferable to avoid the error as Geoff has suggested. Something like this:

If xunits = 0 Then
  lcleaninvoice = 0
Else
  lcleaninvoice = xrevenue / xunits
End If