llawrenceg
asked on
Excel Total from Top down
I understand how to write some simple VBA to work with ranges where you start at the bottom and work up row by row.
Now I have to do the accumulation of rows to a total from the top down. but I'm not sure of the syntax
I need to start at Row 21 and calculate up and need some help on how to do it
Now I have to do the accumulation of rows to a total from the top down. but I'm not sure of the syntax
I need to start at Row 21 and calculate up and need some help on how to do it
lnLastRow = .Cells(Rows.Count, 7).End(xlUp).Row
For j = 21 To InLastRow Step+1
If .Cells(j - 1, 10).Value < .Cells(8, 2) And (.Cells(j - 1, 10) + .Cells(9, 2)) < .Cells(8, 2) Then
.Cells(j, 9).Value = .Cells(9, 2).Value
ElseIf .Cells(j - 1, 10) >= .Cells(8, 2) Then
.Cells(j, 9).Value = 0
Else: .Cells(j, 9).Value = (.Cells(8, 2) - .Cells(j, 10))
End If
Next j
The correct Syntax it will be...
For j = InLastRow Step To 21 Step -1
Saurabh...
For j = InLastRow Step To 21 Step -1
Saurabh...
Hey, it's ellipses dude!
Lol@Kevin, Man now you gonna ensure that i stop using them.
Saurabh
Saurabh
You have no idea what I'm capable of ;-)
Nah, I certainly can take a wild guess for sure.
You think?
Yes, i do.
ASKER
I'm trying to go from row 21 to the Last Row . So the sequence would be
Row 21, 22,23,24,25,26 ..... Last Row
Row 21, 22,23,24,25,26 ..... Last Row
Guys, get a room.
llawrenceg, your loop is right for what you want to achieve, but your code might be off. What exactly are you trying to achieve? If it's easier, you can upload a sample file with source and desired results.
Thomas
llawrenceg, your loop is right for what you want to achieve, but your code might be off. What exactly are you trying to achieve? If it's easier, you can upload a sample file with source and desired results.
Thomas
ASKER
I am try to code the following formula:
where I20 and J20 = 5
J21=IF(G20>0, $I21+J20, "")
for all cells from J21 to the Last Cell in the Column J
where I20 and J20 = 5
J21=IF(G20>0, $I21+J20, "")
for all cells from J21 to the Last Cell in the Column J
something like this?
Thomas
Thomas
lnLastRow = .Cells(Rows.Count, 7).End(xlUp).Row
For j = 21 To InLastRow Step+1
If .Cells(j - 1, "I")=5 and .Cells(j - 1, "J")=5 and .Cells(j - 1, "G")>0 then
cells(j,"J")=cells(j-1,"J")+cells(j,"I")
else
cells(j,"J").clearcontents
End If
Next j
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Saurabh726
I'm getting a run time error
Application defined or Object defined error
I'm getting a run time error
Application defined or Object defined error
you should not get that error message which line you get this error..?? and what is your complete code..?
Saurabh...
Saurabh...
ASKER
saurabh726:
The error was my fault.
The error was my fault.
ASKER
Elegant solution
For j = 21 To InLastRow Step+1
To this:
For j = 21 To InLastRow Step -1
This will step through it backwards.