The Error That Stumps Me

  • Thread starter Thread starter Alex A
  • Start date Start date
A

Alex A

I am getting this error:
Run-time error '6': Overflow

This is the line of code that is trying to calculate the
division of two fields on the Excel Spreadsheet, then
subract 1 and then multiply by 100: I set up the
variable SinglePercentChange as Single. But still I get
that error message.

SinglePercentChange = (AddColumn1FieldRange.Offset
(RowVal1, ColVal1).Value / AddColumn1FieldRange.Offset
(RowVal2, ColVal2) - 1) * 100

TargetRange.Offset(intRowIndex3, ColumnCountIVSLH2).Value
= SinglePercentChange

Anyone got the VB Kung foo move for this?
Thanks.
Alex

Below is the relevant block of code:

'Return PercentChange Column
intLastRowIndexIVSLH2 = intRowCountIVSLH2 +
TargetRange.Row - 1
intLastRowEndIVSLH2 = intRowCountIVSLH2 - 1 'Never could
figure out why this needs to be -1.

LoopCounter1 = 0
RowVal1 = 0
ColVal1 = -1
RowVal2 = -1
ColVal2 = -1

For intRowIndex3 = (intFirstRowStartIVSLH2 + 1) To
intLastRowEndIVSLH2 Step 1

If LoopCounter1 >= 1 Then
Set AddColumn1FieldRange = TargetRange.Offset
(intRowIndex3, intColumnCountIVSLH2)
SinglePercentChange = (AddColumn1FieldRange.Offset
(RowVal1, ColVal1).Value / AddColumn1FieldRange.Offset
(RowVal2, ColVal2) - 1) * 100
TargetRange.Offset(intRowIndex3,
intColumnCountIVSLH2).Value = SinglePercentChange
LoopCounter1 = LoopCounter1 + 1

Else
TargetRange.Offset(intRowIndex3,
intColumnCountIVSLH2).Value = "N/A"
LoopCounter1 = LoopCounter1 + 1
End If

Next intRowIndex3
 
SinglePercentChange = (csng(AddColumn1FieldRange.Offset _
(RowVal1, ColVal1).Value) / csng( AddColumn1FieldRange.Offset _
(RowVal2, ColVal2)) - 1!) * 100!
 
Okay, I broke down the Code into three variables...

SinglePercentChange
SingleNumerator1
SingleDenominator1

Making sure that all were of the same datatype surely
helped. I had a few results that still gave the Overflow
error. But I think I discovered why there were still
some like that... it was a Divide by Zero error!
Got it!

Awesome, Thanks. Seems fine now.
Take Care
Alex.
 
Back
Top