Add Column and percent macro (excel xp)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

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

I have also attached your workbook with the VBA routine.

Reply | Quote