x
Solved

# XL Macro to add every 4th row cell to total

Posted on 2000-05-08
Medium Priority
331 Views
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
Question by:bsnare
• 7
• 5

LVL 1

Expert Comment

ID: 2789943
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

Author Comment

ID: 2789994
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

LVL 1

Expert Comment

ID: 2790078
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

Author Comment

ID: 2790082
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

LVL 1

Expert Comment

ID: 2790087
btw you want to sum the budget right???
0

Author Comment

ID: 2790094
The total would be at the bottom, however many rows down that is. Thanks -
0

LVL 1

Expert Comment

ID: 2798876
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

Author Comment

ID: 2800743
Adjusted points from 50 to 200
0

Author Comment

ID: 2800744
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

Author Comment

ID: 2800783
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

LVL 1

Accepted Solution

ntdragon earned 800 total points
ID: 2802394
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

Author Comment

ID: 2804621
Thank you so much -

Bob
0

## Featured Post

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.

## Join & Write a Comment Already a member? Login.

Windows programmers of the C/C++ variety, how many of you realise that since Window 9x Microsoft has been lying to you about what constitutes Unicode (http://en.wikipedia.org/wiki/Unicode)? They will have you believe that Unicode requires you to use…
There's never been a better time to become a computer scientist. Employment growth in the field is expected to reach 22% overall by 2020, and if you want to get in on the action, it’s a good idea to think about at least minoring in computer science …
The goal of this video is to provide viewers with basic examples to understand how to create, access, and change arrays in the C programming language.
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
###### Suggested Courses
Course of the Month10 days, 5 hours left to enroll

#### 606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.