• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

XL Macro to add every 4th row cell to total

A common spreadsheet problem.
How to add the value in the (nth) 4th row in a column to total row at bottom of the range. Data is uniformly formatted in this fashion.
example:

Cell - Value
A1 = 10
A2 = 3
A3 = 7
A4 = 5
A5 = 2
....
Total = 12 (Rows 1 and 5 only)

Thanks -
Bob

0
bsnare
Asked:
bsnare
  • 7
  • 5
1 Solution
 
ntdragonCommented:
i didn't get you exectlly can you be more clear
about going to a cell there are the function cell() and the func range()
0
 
bsnareAuthor Commented:
ntdragon
A worksheet contains:

GL#      Data                      Hours
-------------------------------------      
6010-32-00-0006010      Budget       16
Supervisory - Labor      Actual       24
        
6020-32-00-0006020      Budget       144
Admisistrative  Labor      Act       225              
6070-32-00-0006070      Budget       8
Training                Actual       1             
7020-32-00-0007020      Budget       0
Recruitment Labor      Actual       5             
7050-32-00-0007050      Budget       4
Bid and Proposal       Actual       0
-------------------------------------
(continues for 500 lines)

                 Total Budget:  ???

How to create a formula or macro to total only the "Budget" row hours?
Assume the "Budget" row is every 3rd row through row 500.  

Does that help?



0
 
ntdragonCommented:
i"ll write it for you tomorrow 'cause i'm not at home right now

but another question where do you want the result to be stored???
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bsnareAuthor Commented:
ntdragon
A worksheet contains:

GL#      Data                      Hours
-------------------------------------      
6010-32-00-0006010      Budget       16
Supervisory - Labor      Actual       24
        
6020-32-00-0006020      Budget       144
Admisistrative  Labor      Act       225              
6070-32-00-0006070      Budget       8
Training                Actual       1             
7020-32-00-0007020      Budget       0
Recruitment Labor      Actual       5             
7050-32-00-0007050      Budget       4
Bid and Proposal       Actual       0
-------------------------------------
(continues for 500 lines)

                 Total Budget:  ???

How to create a formula or macro to total only the "Budget" row hours?
Assume the "Budget" row is every 3rd row through row 500.  

Does that help?



0
 
ntdragonCommented:
btw you want to sum the budget right???
0
 
bsnareAuthor Commented:
The total would be at the bottom, however many rows down that is. Thanks -
0
 
ntdragonCommented:
i still not at home but try this
i'm not sure about the ord()
i don't remember the name of the function that returns the assci code of the char

dim i
dim sum
sum=0
for i=1 to 500
sum=sum+ord(Cells(3,i))-ord("0");
next i
i=i+1
Cells(1,i)="Total"
Cells(2,i)="="
Cells(3,i)=chr(sum++ord("0"))


0
 
bsnareAuthor Commented:
Adjusted points from 50 to 200
0
 
bsnareAuthor Commented:
ntdragon

I have tried this but did get hung up on the "ord" function. I also tried Char and Asc but am not getting it. Is the ORD function adding the contents of the cell to the Sum? I guess I don't understand how that is happening. Can you clarify a bit? Thanks

Bob
0
 
bsnareAuthor Commented:
I see I screwed up here. This question should be in the Excel area rather than "C" programming. Oh well - If you can't help further it's quite understandable. I should re-ask this in the right forum. Thank you very much for your help anyway.

Bob
0
 
ntdragonCommented:
try this:
dim i
dim sum
sum=0
for i=1 to 500
sum=sum+Asc(Cells(3,i))-Asc("0");
next i
i=i+1
Cells(1,i)="Total"
Cells(2,i)="="
Cells(3,i)=chr(sum+Asc("0"))

what i mean is
i want to change the data in the cell
into a number to sum it and then back to string to write it back into the last cell




0
 
bsnareAuthor Commented:
Thank you so much -

Bob
0

Featured Post

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.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now