Summing with Macro

[tjohn176]

New to macros, please help. Thank you!

A file is downloaded?/imported? into Excel 2002. It is about 5 columns
by 9,000 rows; and of course everything is just text and numbers with
no formulas. As follows:

Column A is text and B thru E are numbers. B is quantity, C is price
and D is the extended total.

I want to be able to make changes to B and C so the total in D will
change so I need to make column D a formula (B*C).

There may be a hundred of spaces in the rows between 1 and 9,000 and
each one of those breaks, which I want to represent a subtotal, has to
be summed to the top of the group of numbers just before the next
break. In other words, I need several hundred subtotals for the
particular numbers between each break (or blank space) in Column D.

One more wrinkle, this file is a parts listing for 11 companies and I
would like to have a grand total for each company (e.g. the first 20
subtotals are company 1:the next 10 are company 2 and so on to the end)
Then, I would like a Grand total of the entire file(all 11 companies)

This file is downloaded a couple times each month and needs to be
manipulated.

Can anyone help me with this sideways multiplication and bottom summing
with a macro that does not take forever to execute?

I promise, I have tried to do this on my own. Thanks!!

 

[Pikus]

I'll be happy to take a look if you send it to me. - Pikus
Don't forget to include any necessary details.

 

[tjohn176]

I don't have the file at home, but the data is like this:

Col A Col B Col C Col D Col E
COMPANY #1
Part# Quantity Price Total Not Important
c10 3 1.75 5.25
a602 5 2.12 10.60
ch16 1 0.50 0.50
subtotal (created b
macro)

g15 10 1.20 12.00
gh-12 4 2.00 8.00
a602 6 2.12 12.72
d42 8 0.75 6.00
subtotal

ch16 7 0.50 3.50
r-34 2 1.00 2.00
subtotal
Grand Total for Company #1 $xxxxxxxxx

COMPANY #2
g15 6 8.00 48.00
r-34 1 1.00 1.00
subtotal

t10 12 0.40 4.80
etc, etc, etc
......
......
subtotal
Grand Total for Company #2 $xxxxxxxxx

COMPANY #3
Same type of info here all the way down through COMPANY #11

Grand Total for Company #11 $xxxxxxxxx

Grand Total for all 11 Companies $xxxxxxxxx




Again, I just want a macro that can multiply the non-empty rows fo
Columns B & C ,like (B*C) and put the total in column D as a formula
so when I change B or C then Column D will change.

I also want Column D to have subtotals at each break that sums th
extended total of the parts above it but only up to the next break.

Finally, I would like to have a Grand Total of Column D for eac
COMPANY and then a Final Grand Total for all 11 Companies.

I have been doing an Edit/Fill/Series/StepValue=1 in Excel and goin
from 1 to 9000 in Column F, then Sort on Column C to move out all o
the blank rows and text rows, then just doing (B*C) in Column D, the
Copying all the way down Column D to the end.

Then, I Re-Sort on Column F to get the file back to its original stat
but now with a formula in Column D.

I would like this to be a macro or some other kind of macro that wil
get the formula in Col D. I also want the subtotals at the breaks an
the Grand Totals by Company, etc. These, I have ben doing manuall
with "Sum" which takes over an hour

 

[Tom Ogilvy]

Dim rng as Range
set rng = Columns(3).specialCells(xlConstants,xlNumbers)
set rng1 = Intersect(rng.EntireRow,Columns(4))
rng1.formula = "=" & rng1(1,-1).Address(0,0) & _
"*" & rng1(1,0).address(0,0)

Will put in your formulas

Zip the text file and send it to me and I will suggest a method to do the
rest.