Copying formulas into all cells of a column Macro

[Craig13_13]

Hello Excel Gurus!

Hopefully I can explain my dilemma, a bit of background: it's a supplier score monthly tracker, very basic, list of suppliers in column A then Jan scores in column B, Feb scores in column C etc etc.

I've been working on Macros for arranging by score based on the last month using the (xlToRight) command, but I'm now working on a similar one try to copy a formula into every cell of the nerest blank column, for example if March (i.e. column D) is empty. I can get it to find the first empty column, I can get it to put the formula into that first cell but I can't get it to copy the formula into every cell of that column (bare in mind its not always going to be column D thats the empty one, thats why I'm using the xlToRight function). Can anyone help me with this?

Thanks very much!

 

[TheNoocH]

see if something like this will work

Sub test()
    lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
    lastrow = Cells(Rows.Count, lastcol).End(xlUp).Row
    Range(Cells(2, lastcol), Cells(lastrow, lastcol)).Copy Cells(2, lastcol + 1)
End Sub

 

[jim may]

If your first data row is row 2 then (to get destination cell for pasteing) use

Range("IV2").Activate
Selection.End(xlToLeft).Offset(0, 1).Select

 

[jim may]

a Perhaps better approach is

Sub Macro1()
'
Set MyDes = Range("IV2").End(xlToLeft).Offset(0, 1)
Range("B2:B5").Copy MyDes

End Sub