Solved

Posted on 2012-03-25
337 Views
So that I can award more points, I am breaking down my requirements into several parts.  Please refer to the attached Excel file.

Part A:
I would like a macro which:
-      Formats the numbers in Cols E to H as numbers with 1000 separator (,) and  no decimal places
-      Inserts column totals in Cols E to H
-      Inserts cross-totals in Col I

Thanks.
Book1.xlsx
0
Question by:RishiSingh05
• 2

LVL 33

Accepted Solution

Norie earned 500 total points
ID: 37762672
I wasn't 100% sure what you meant by cross-sums but I've added a formula in each row of I that sums columsn E to H.

Also, I wasn't sure if you wanted that sum to also include the row with the column sums, if you do use this.
``````    Set rng = Range("I2:I" & LastRow+1)

``````

Anyway, here's the code.
``````Dim rng As Range
Dim LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Set rng = Range("E2:H" & LastRow)

rng.NumberFormat = "#,000"

Set rng = Range("E" & LastRow + 1 & ":H" & LastRow + 1)

rng.Formula = "=SUM(R2C:R[-1]C)"

Set rng = Range("I2:I" & LastRow)

rng.Formula = "=SUM(E2:H2)"
``````
0

Author Comment

ID: 37762678
Thanks.  I will test it out and let you know.
0

Author Comment

ID: 37762682
I will post Part B shortly.  Thanks.
0