Overflow error, need help

I have been writing a macro that does the exact same thing, it adds a row and calculates the previous row divided by the bottom of the previous row, which is a total. This gives the percentage of the total. This all worked fine.
Then I had to add two rows and take the percentage of that. I made a new row that was the addition of the two previous rows. That works fine, but now the maco that was working on all the other rows, wont work on the new row. It gives me an overflow error and I cant figure out why... any suggestions?

Here is a snippet of my macro. The green text is the part that works. The blue text is the addition part, which also works but could be leading to the overflow. The yellow part is the area that does not work, and the red is where the overflow is specifically occuring:

Columns("ac:ac").Select
Selection.Insert Shift:=xlToRight

Dim varTotal, var7, var8
var7 = Range("ac2")
var8 = Range("ab1").Select
Selection.End(xlDown).Select
ReportLastRow = ActiveCell.Row
varTotal = var7 / var8
Range("ac2").Select
ActiveCell.FormulaR1C1 = _
varTotal

Set r = Range("ab1").End(xlDown)
s = r.AddressLocal(True, True, xlR1C1)
For i = 1 To r.Row
Range("ac2").Select
ActiveCell.FormulaR1C1 = "=RC[-1] / " & s
Next i
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown


Columns("af:af").Select
Selection.Insert Shift:=xlToRight
Range("Af1").Select
ActiveCell.FormulaR1C1 = "FIX & COM"
Range("af2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]"
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown
Columns("ag:ag").Select
Selection.Insert Shift:=xlToRight

Dim blahTotal, var9, var10

var7 = Range("ag2")
var8 = Range("af1").Select
Selection.End(xlDown).Select
ReportLastRow = ActiveCell.Row
blahTotal = var9 / var10
Range("ag2").Select
ActiveCell.FormulaR1C1 = _
blahTotal

Set r = Range("af1").End(xlDown)
s = r.AddressLocal(True, True, xlR1C1)
For i = 1 To r.Row
Range("ag2").Select
ActiveCell.FormulaR1C1 = "=RC[-1] / " & s
Next i
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown

Are you doing this in VBScript ?
If not, declare the data types of your variables. So what do expect the
value of var7 to be:
Dim varTotal, var7, var8
var7 = Range("ac2")
Is a range object, the cell value ?
(Ok, there's no Set so you can tell it does not involve objects, but...)

Also, what value do you expect for var8 here:
var8 = Range("ab1").Select
(I get a value of var8=True)
So what do you expect the result to be here:
varTotal = var7 / var8=Range("AC2").Value/-1

NickHK


"mkerstei" <[email protected]> wrote in
message news:[email protected]...
>
> I have been writing a macro that does the exact same thing, it adds a
> row and calculates the previous row divided by the bottom of the
> previous row, which is a total. This gives the percentage of the
> total. This all worked fine.
> Then I had to add two rows and take the percentage of that. I made a
> new row that was the addition of the two previous rows. That works
> fine, but now the maco that was working on all the other rows, wont
> work on the new row. It gives me an overflow error and I cant figure
> out why... any suggestions?
>
> Here is a snippet of my macro. The green text is the part that works.
> The blue text is the addition part, which also works but could be
> leading to the overflow. The yellow part is the area that does not
> work, and the red is where the overflow is specifically occuring:
>
> Columns("ac:ac").Select
> Selection.Insert Shift:=xlToRight
>
> Dim varTotal, var7, var8
> var7 = Range("ac2")
> var8 = Range("ab1").Select
> Selection.End(xlDown).Select
> ReportLastRow = ActiveCell.Row
> varTotal = var7 / var8
> Range("ac2").Select
> ActiveCell.FormulaR1C1 = _
> varTotal
>
> Set r = Range("ab1").End(xlDown)
> s = r.AddressLocal(True, True, xlR1C1)
> For i = 1 To r.Row
> Range("ac2").Select
> ActiveCell.FormulaR1C1 = "=RC[-1] / " & s
> Next i
> If IsEmpty(ActiveCell) Then Exit Sub
> Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0,
> 1)).FillDown
>
>
> Columns("af:af").Select
> Selection.Insert Shift:=xlToRight
> Range("Af1").Select
> ActiveCell.FormulaR1C1 = "FIX & COM"
> Range("af2").Select
> ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]"
> If IsEmpty(ActiveCell) Then Exit Sub
> Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0,
> 1)).FillDown
> Columns("ag:ag").Select
> Selection.Insert Shift:=xlToRight
>
> Dim blahTotal, var9, var10
>
> var7 = Range("ag2")
> var8 = Range("af1").Select
> Selection.End(xlDown).Select
> ReportLastRow = ActiveCell.Row
> blahTotal = var9 / var10
> Range("ag2").Select
> ActiveCell.FormulaR1C1 = _
> blahTotal
>
> Set r = Range("af1").End(xlDown)
> s = r.AddressLocal(True, True, xlR1C1)
> For i = 1 To r.Row
> Range("ag2").Select
> ActiveCell.FormulaR1C1 = "=RC[-1] / " & s
> Next i
> If IsEmpty(ActiveCell) Then Exit Sub
> Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0,
> 1)).FillDown
>
>
> --
> mkerstei
> ------------------------------------------------------------------------
> mkerstei's Profile:
http://www.excelforum.com/member.php...o&userid=25688
> View this thread: http://www.excelforum.com/showthread...hreadid=560783
>

Yeah, I'm at least trying to do this in VB Script. But the values of both var7 and var8 are numbers with some amount of decimals if that makes any difference. The result will be numbers as well.

Have you tested the value of var8 ?
var8 = Range("ab1").Select

NickHK

"mkerstei" <[email protected]> wrote in
message news:[email protected]...
>
> Yeah, I'm at least trying to do this in VB Script. But the values of
> both var7 and var8 are numbers with some amount of decimals if that
> makes any difference. The result will be numbers as well.
>
>
> --
> mkerstei
> ------------------------------------------------------------------------
> mkerstei's Profile:
http://www.excelforum.com/member.php...o&userid=25688
> View this thread: http://www.excelforum.com/showthread...hreadid=560783
>