Macro for Autofill Horintontally HELP!!

[nich0142]

If you have a column of data (let's say A1 to A5) and you create a formula in B1 linking to A1 (let's say B1=A1 *2 or what ever you want) you can simply double click in the lower right corner and it will Automatically fill Cells B2 through B5. It knows to automatically fill until there is a null cell in Column A.

Does anyone have a macro (or can help define a macro) that would do the same thing for a horizontal application?

For instance I have values in C1 to C120. I would like to be able to place a formula in Cell B1 linking to C1 (let's say B1 = C1 * 2). I would then have Cell B1 active and then run the macro which would automatically fill in Cells B2 to B120 (i.e. B2 = C2 *2, B3 = C3*2, etc.. ). The macro would know when there is a null cell in row C and it doesn't need to copy further.

Thanks a ton for your help!!!

 

[nich0142]

Sorry I screwed up my example.

What I meant to say is if I have values in A5 to H5 and I have a cell in A4 linked to A5 (say A4 = A5*2). I want a macro that will automatically fill B4 to H4 such that (B4 = B5*2, C4 = C5*2, etc..) until H4 which would equal H4 = H5 * 2. The macro would then know that "I5" is null and would not copy the equation further.

This would be a universal macro (i.e. if I have A4 "Active" and run the macro it examines Row 5, if I have A10 active then the macro would copy the formula across Row 10 and examine Row 11 until a null value).

Thanks

 

[Scott Huish]

Perhaps:

Sub CopyFormula()
Dim x As Long, y As Long
y = ActiveCell.Row
x = Cells(y + 1, Columns.Count).End(xlToLeft).Column
ActiveCell.Copy Range(Cells(y, ActiveCell.Column), Cells(y, x))
End Sub

 

[nich0142]

Works like a charm!!! Thank you so much!!