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

llawrencegAsked:
Who is Participating?
 
Saurabh Singh TeotiaConnect With a Mentor Commented:
A shorter way to do what you are looking for is do by the following code in 1 go without code.
Saurabh.

lnlastrow = .Cells(Rows.Count, 7).End(xlUp).Row
.Range("J21:J" & lnlastrow).Formula = "=IF(G20>0, $I21+J20, """")"
.Range("J21:J" & lnlastrow).Value = .Range("J21:J" & lnlastrow).Value

Open in new window

0
 
TracyVBA DeveloperCommented:
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.
0
 
Saurabh Singh TeotiaCommented:
The correct Syntax it will be...
For j =  InLastRow Step To 21 Step -1
Saurabh...
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
zorvek (Kevin Jones)ConsultantCommented:
Hey, it's ellipses dude!
0
 
Saurabh Singh TeotiaCommented:
Lol@Kevin, Man now you gonna ensure that i stop using them.
Saurabh
0
 
zorvek (Kevin Jones)ConsultantCommented:
You have no idea what I'm capable of ;-)
0
 
Saurabh Singh TeotiaCommented:
Nah, I certainly can take a wild guess for sure.
0
 
zorvek (Kevin Jones)ConsultantCommented:
You think?
0
 
Saurabh Singh TeotiaCommented:
Yes, i do.
0
 
llawrencegAuthor Commented:
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
0
 
nutschCommented:
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
0
 
llawrencegAuthor Commented:
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
0
 
nutschCommented:
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

0
 
llawrencegAuthor Commented:
Saurabh726
I'm getting a run time error
Application defined or Object defined error
0
 
Saurabh Singh TeotiaCommented:
you should not get that error message which line you get this error..?? and what is your complete code..?
Saurabh...
0
 
llawrencegAuthor Commented:
saurabh726:
The error was my fault.
0
 
llawrencegAuthor Commented:
Elegant solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.