Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Automate Excel tasks

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
RishiSingh05
Asked:
RishiSingh05
  • 2
1 Solution
 
NorieCommented:
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)
    

Open in new window


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)"

Open in new window

0
 
RishiSingh05Author Commented:
Thanks.  I will test it out and let you know.
0
 
RishiSingh05Author Commented:
I will post Part B shortly.  Thanks.
0

Featured Post

Industry Leaders: 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!

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