Macro o insert formula in new columns

[Ulisses_Carso]

I have a sheet with X column numbers, I need to insert a column between each one and then I need to insert a formula in each new column.

I already made the code to insert these columns, but I'm stuck in the part of inserting the formula.
Note: The formula will reference and fill down with the cell to its left.

I think a loop could do this, but i don't know how to use it, can anyone help me?

Sub InsertBlankColumns()
    Dim MyRange As Range
    Dim iCounter As Long
    Set MyRange = Range("A:AI")

    For iCounter = MyRange.Columns.Count To 2 Step -1
        MyRange.Columns(iCounter).EntireColumn.Insert
    LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
    Next iCounter
End Sub

 

[offthelip]

You didn't say what formula you wanted in the column so I have put a simple "sum"

Sub InsertBlankColumns()
    Dim MyRange As Range
    Dim iCounter As Long
    Set MyRange = Range("A:AI")
    lastrowcolumnA = Cells(Rows.Count, 1).End(xlUp).Row

    For iCounter = MyRange.Columns.Count To 2 Step -1
        MyRange.Columns(iCounter).EntireColumn.Insert
        Range(Cells(1, iCounter - 1), Cells(lastrowcolumnA, iCounter - 1)).Formula = "=sum(A1:A" & lastrowcolumnA & ")"
    Next iCounter
End Sub

 

[Ulisses_Carso]

Hello Offhelip,
I just saw your message today, that line is working, i only removed that " - 1" in both iCounter and it worked with reservations.
1. I don't know why your line isn't accepting my formula i'm using an "IF" as example "if(a1<>"";"1";"2") because the real is a big IF with 15 conditions
2. is there a way to insert a column after column A, the code inserts a column to its left and the formula there, but don't do it in column A

I'm trying to solve this here, but your help is very welcome again.


Here is the line i modified, i tryed to use .FormulaLocal (i think because i'm from Brasil and all my formulas are in PT-BR) instead only .Formula but din't work too

Range(Cells(1, iCounter), Cells(LastRowColumnA, iCounter)).Formula = "=SE(A1<>"""";""1"";""2"")" & LastRowColumnA & ")"

I think it's missing ( but I didn't find where

 

[Fluff]

Try

Range(Cells(1, iCounter), Cells(LastRowColumnA, iCounter)).Formula = "=IF(A1<>"""",""1"",""2"")"

 

[Ulisses_Carso]

You did it, why i don't need that end of code "& LastRowColum....." with my formula? I didn't get it.

 

[Fluff]

You don't need it as you are just looking at a single cell in col A on each row.