Double Click Fill Handle - Macro Equivalent

[pgrad]

Hi All,

I am trying to replicate a formula across the worksheet rather than down as a small part of a macro.

Dim rng as Range 
set rng = Range(cells(2,1),cells(2,1).End(xlDown)) 
rng.offset(0,1).Formula = Cells(2,2).Formula

It copies down just fine, so I thought I would replace the xldown with xlToRight.

Dim rng as Range 
set rng = Range(cells(2,1),cells(2,1).End(xlToRight)) 
rng.offset(0,1).Formula = Cells(2,2).Formula

Sadly I out of my depth now so was hoping someone could show me the next step.

I'm getting an Application Defined or Object Defined Error on this code -

rng.offset(0,1).Formula = Cells(2,2).Formula

Thanks
Paul

 

[RSXchin]

Sub heres_for_you_to_change()
    'this is just to show you how it can work
    Cells(1, 1).Formula = "a1's formula"
    Range(Cells(1, 2), Cells(1, 10)).Formula = Cells(1, 1).Formula
End Sub

 

[pgrad]

Hey,

Thanks for your quick reply! Apologies I was away from my desk most of the day so have only just got back on with this.

I am afraid to say I could not adapt what you have provided to suit my needs. Perhaps you could explain what the code is doing, in laymans terms?

Alternativley I have almost got a workaround but cannot figure out how to pull the row number from the active cell part of my code.

'Sum up columns below last row
Last_Row = ActiveSheet.Range("b65536").End(xlUp).Row
ActiveSheet.Range("B" & Last_Row + 1).Formula = "=Sum(B1:B" & Last_Row & ")" ' Change B to whatever column you need to sum up.
 
'Finds last used cell in column b
Range("B65536").End(xlUp).Select
 
'Replicates formula across sheet
Selection.Copy
    Range("C4:IV4").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas
    Calculate

Row 4 could potentially be any row from 1 to 10000, and I will need to sum up every column in b:iv.

Thanks again fro looking at this!

Paul