Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error, need help
I have been writing a macro that does the exact same thing, it adds row and calculates the previous row divided by the bottom of th previous row, which is a total. This gives the percentage of th total. This all worked fine. Then I had to add two rows and take the percentage of that. I made new row that was the addition of the two previous rows. That work fine, but now the maco that was working on all the other rows, won work on the new row. It gives me an overflow error and I cant figur 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 b leading to the overflow. The yellow part is the area that does no 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)).FillDow -- mkerste ----------------------------------------------------------------------- mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568 View this thread: http://www.excelforum.com/showthread.php?threadid=56078 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error, need help
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 he var8 = Range("ab1").Select (I get a value of var8=True) So what do you expect the result to be he varTotal = var7 / var8=Range("AC2").Value/-1 NickHK "mkerstei" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error, need help
Yeah, I'm at least trying to do this in VB Script. But the values o both var7 and var8 are numbers with some amount of decimals if tha makes any difference. The result will be numbers as well -- mkerste ----------------------------------------------------------------------- mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568 View this thread: http://www.excelforum.com/showthread.php?threadid=56078 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Overflow error, need help
Have you tested the value of var8 ?
var8 = Range("ab1").Select NickHK "mkerstei" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overflow error.. why? | Excel Discussion (Misc queries) | |||
Overflow error | Excel Programming | |||
Overflow Error | Excel Programming | |||
Overflow error | Excel Programming | |||
overflow error | Excel Programming |