Why aren't my newly inserted columns being populated with the Formula?

[Milbourn]

Hey guys!

First the code:

Sub MyInsertColumn()
 
Dim wks As Worksheet
Dim i As Long
Dim Lastrow As Long
Application.ScreenUpdating = False


Lastrow = Range("A" & Rows.Count).End(xlUp).Row


For Each wks In ActiveWindow.SelectedSheets
    With wks
        For i = .Cells.SpecialCells(xlCellTypeLastCell).Column To 2 Step -1
            .Columns(i).Insert
            Range(Cells(2, i), Cells(Lastrow, i)).Formula = "=SUM(a2,b2)"
            .Columns(i).Insert
            Range(Cells(2, i), Cells(Lastrow, i)).Formula = "=SUM(a2,b2)"
        Next i
    End With
Next wks


End Sub

So I have this to add two columns and populate with formulas for the entire worksheet

The code runs fine, and adds the columns but does not add the formulas. Is there something I'm missing?

Thanks,

M

 

[Fluff]

It is adding the formulae, but it's only adding them to whatever sheet is active when you run the code.
Also unless you only have 4 columns of data it's adding more than 2 columns.
Can you please explain what you are trying to do.

 

[Milbourn]

Hi Fluff!

First, thanks for taking the time to reply! I appreciate it.

So I am trying to add in 2 columns next to every column in a given worksheet and then add some formulas in those new columns.

Right now this code is adding in the 2 new columns as I'd hope for on the active sheet, but no formulas (and i've checked every sheet!)

Does this help? Any advice would be welcome!!

M

 

[footoo]

It should work but I think you need to change :

For i = .Cells.SpecialCells(xlCellTypeLastCell).Column To 3 Step -1

 

[Fluff]

How about

Sub Milbourn()
   Dim Ws As Worksheet
   Dim UsdRws As Long, i As Long

   Application.ScreenUpdating = False
   For Each Ws In ActiveWindow.SelectedSheets
      With Ws
         UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
         For i = .Cells(1, Columns.Count).End(xlToLeft).Column To 2 Step -1
            .Columns(i).Resize(, 2).Insert
            .Range(.Cells(2, i), .Cells(UsdRws, i + 1)).Formula = Array("=SUM(a2,b2)", "=sum(a2,b2)")
         Next i
      End With
   Next Ws
End Sub

 

[footoo]

For i = .Cells(1, Columns.Count).End(xlToLeft).Column To [COLOR=#ff0000]3 [/COLOR]Step -1

 

[Milbourn]

Fluff that's amazing it worked perfectly. Thank you so much

Footoo, thank you for your time too. I don't think the Column To 3 was the solution though i did give it a go. It provided the same result (though missed adding the new columns to Col A)

Thank you all, this really helps

M

 

[Fluff]

You're welcome & thanks for the feedback

 

[footoo]

Fluff that's amazing it worked perfectly. Thank you so much

Footoo, thank you for your time too. I don't think the Column To 3 was the solution though i did give it a go. It provided the same result (though missed adding the new columns to Col A)

Thank you all, this really helps

M

I don't understand how it can do what you want without my suggested amendment since column B is referenced in the formula being inserted.

 

[Fluff]

As the OP is putting the same formula in all the new columns, I suspect that they are not the correct formula, but just an example.