[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 784
  • Last Modified:

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

0
llawrenceg
Asked:
llawrenceg
  • 6
  • 5
  • 3
  • +2
1 Solution
 
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
 
zorvek (Kevin Jones)ConsultantCommented:
Hey, it's ellipses dude!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
Saurabh Singh TeotiaCommented:
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now