Link to home
Start Free TrialLog in
Avatar of llawrenceg
llawrencegFlag for United States of America

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
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

Open in new window

Avatar of Tracy
Tracy
Flag of United States of America image

Try changing this:

For j = 21 To InLastRow Step+1

To this:

For j = 21 To InLastRow Step -1

This will step through it backwards.
The correct Syntax it will be...
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
You have no idea what I'm capable of ;-)
Nah, I certainly can take a wild guess for sure.
Avatar of llawrenceg

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
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
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
something like this?

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Saurabh726
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...
saurabh726:
The error was my fault.
Elegant solution