Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Add Column and percent macro (excel xp)

    I need to add some columns to the attached worksheet and put a "%" in that column for some cells. When I use

    Columns("C:C").Select 'insert cols for % signs
    Selection.Insert Shift:=xlToRight

    for some reason it selects COL B and COL C. That is my first problem.

    Next, for each "table", I have to add a % in the new column for the rows that have text in COLUMN A. So I need a % sign in C10, G10...
    Lastly if there is a 100 under a "text" line like on B11, I need to clear the contents of the cell but only in COL B.

    Please see Sheet2 for the correct version i'm trying for (i didn't add all the % signs tho)

    I was thinking of finding a startrow and endrow based on the word "Base: " and "Sigma" and then putting the % signs in each of those "sections" and looping thru but I didn't get very far. Please help me in my logic. thanks
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Column and percent macro (excel xp)

    I will address your first problem and not talk about the others until you decide how to solve that one.

    You have several merged cells in column C. I have not been able to find a way to insert a column after column C using VBA code as long as there are merged cells in the column. This is just one of many problems that merged cells cause. You could have code to remove the merged cells, insert the column, then re-merge the cells, but this is going to get a bit complex if the merged cells are not fixed in number and location.
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Column and percent macro (excel xp)

    Please ignore my previous reply, it was too late last night when I replied.

    The reason that you get both columns selected when you try to select column C is because you have merged cells that span column C. It is almost never necessary to select a cell or cells when using VBA, and it is always better not to change the selection unless it it absolutely necessary or if you want to have different cells selected when you return control to the user. The VBA code below should do what you want, and does not change the selection. Please also note that this code relies on the text in column A having a left parenthesis as its first character on the rows where you want the percent signs inserted. That was the only easy way I could see to determine which rows needed the percent.

    <pre>Sub AddBlankColumns()
    Dim I As Long, J As Long, lLastRow As Long
    lLastRow = Worksheets("Sheet1").Range("A65536").End(xlUp).Row - 1
    With Worksheets("Sheet1")
    For I = 21 To 0 Step -3
    .Range("C1").Offset(0, I).EntireColumn.Insert
    .Range("C1").Offset(0, I).EntireColumn.ColumnWidth = 2.17
    Next I
    For I = 0 To lLastRow
    If Left(.Range("A1").Offset(I, 0).Value, 1) = "(" Then
    For J = 0 To 28 Step 4
    .Range("C1").Offset(I, J).Value = "%"
    Next J
    If .Range("B1").Offset(I + 1, 0).Value = 100 Then
    .Range("B1").Offset(I + 1, 0).Value = ""
    End If
    End If
    Next I
    End With
    End Sub
    </pre>


    I have also attached your workbook with the VBA routine.
    Attached Files Attached Files
    Legare Coleman

Posting Permissions

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