Macro o insert formula in new columns

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,492
Office Version
  1. 2010
Platform
  1. Windows
You didn't say what formula you wanted in the column so I have put a simple "sum"
VBA Code:
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

New Member
Joined
Sep 4, 2020
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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 o_O
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

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Range(Cells(1, iCounter), Cells(LastRowColumnA, iCounter)).Formula = "=IF(A1<>"""",""1"",""2"")"
 

Ulisses_Carso

New Member
Joined
Sep 4, 2020
Messages
28
Office Version
  1. 365
Platform
  1. Windows
You did it, why i don't need that end of code "& LastRowColum....." with my formula? I didn't get it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
You don't need it as you are just looking at a single cell in col A on each row.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,936
Messages
5,545,104
Members
410,656
Latest member
Hydraulics
Top