Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2000-05-08
Medium Priority
325 Views
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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 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
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
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.

If you are experiencing a similar issue, please ask a related question

Have you thought about creating an iPhone application (app), but didn't even know where to get started? Here's how: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Important pre-programming comments: Iâ€™ve never triâ€¦
Preface I don't like visual development tools that are supposed to write a program for me. Even if it is Xcode and I can use Interface Builder. Yes, it is a perfect tool and has helped me a lot, mainly, in the beginning, when my programs were smallâ€¦
The goal of this video is to provide viewers with basic examples to understand and use structures in the C programming language.
The goal of this video is to provide viewers with basic examples to understand how to use strings and some functions related to them in the C programming language.
###### Suggested Courses
Course of the Month7 days, 1 hour left to enroll