+ Reply to Thread
Results 1 to 3 of 3

Modify a module to insert a column

  1. #1
    Registered User
    Join Date
    11-04-2004
    Posts
    4

    Modify a module to insert a column

    I'm working with the following module which will summarise the selected cells, then place the subtotal in the column 2 places to the right of the column in which the selected cells are. It also allows the user to add a colour to the selection.

    However, i'd like to modify it so that instead of placing the subtotal in the column two columns to the right, it will insert a column and place the subtotal here instead.

    Can anyone help? Heres the module as it is...

    Sub Sub_Total()
    Dim rng As Range, rng1 As Range
    Set rng = Selection
    Set rng1 = rng.Areas(rng.Areas.Count)
    Set rng1 = rng1(rng1.Rows.Count, rng1.Columns.Count)
    rng1.Offset(0, 2).Formula = "=Sum(" & rng.Address & ")"
    rng1.Offset(0, 2).Select
    Application.Dialogs(xlDialogPatterns).Show
    rng.Interior.ColorIndex = _
    rng1.Offset(0, 2).Interior.ColorIndex
    End Sub

    Thanks in advance

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello iwlikin,

    Here is code:

    Sub Sub_Total()
    Dim rng As Range, rng1 As Range
    Set rng = Selection
    Set rng1 = rng.Areas(rng.Areas.Count)
    Set rng1 = rng1(rng1.Rows.Count, rng1.Columns.Count)

    With rng1.Offset(0, 1)
    .Select
    .Insert(xlShiftToRight)
    .Formula = "=Sum(" & rng.Address & ")"
    End With

    .Application.Dialogs(xlDialogPatterns).Show
    rng.Interior.ColorIndex = _
    rng1.Offset(0, 1).Interior.ColorIndex
    End Sub

    _________________________________________________________________

    Hope this helps,
    Leith Ross

  3. #3
    Registered User
    Join Date
    11-04-2004
    Posts
    4
    Thanks for your help, unfortunately although steps in the right direction have been made it is still not quite right.

    The module is inserting a cell, rather than a whole column - can we correct this?

    Additionally, whilst the cells being subtotalled are having colour applied to them, and the newly inserted cell too, the product is still being placed in a cell two columns to the right, instead of the newly inserted column. I'm sure this bit is easily resolvable and i apologise for my ignorance - unfortunately despite my best efforts i am still very much learning all this as i go!

    Thanks in advance for your continue help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1