Pasting Cells into a Bariable Range using VB

[AndrewKent]

Hi there folks,

I'll try and keep this as simple as possible. I'm looking to write a macro that will past a formula down a column for as many rows as there is information in the adjacent column. So for example, cells A3 to A12 has data in them (however this could vary) and I wish to paste a formula from A1 into every cell adjacent to this range and when it reaches A13 (which in this example will be blank) the macro ends.

How do I do this?

Many thanks in advance,

Andy

 

[Datsmart]

Hard to tell what column you want the formulae from cell A1 pasted.
The following code will paste A1's formula to column B starting in cell B3 down to the last row that data exists in column A.

Sub Macro2()
    'Clear old formula
    Range("B3:B" & Range("B65536").End(xlUp).Row & "").ClearContents
    
    'assign new formula
    Range("A1").Copy
    Range("B3:B" & Range("A65536").End(xlUp).Row & "").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[AndrewKent]

Thank you mate,

I've only just had a chance to look at this and I've adapted it to my document and it seems to work fine. Thank you!

Andy