Excel macro formula fill down for block of data

[banjoflanjo]

Hi,

I'm using a macro to insert formulae into a spreadsheet and want to be able to fill them down to the bottom of the block of data.

The user inserts 2 columns to the left of the block of data, selects the cell of the first row in the first inserted column and then I'm using the following code to insert the formulae:

ActiveCell.FormulaR1C1 = _ 
"=IF(LEFT(RC[2],2)="" "",R[-1]C,TRIM(LEFT(RC[2],12)))" 
ActiveCell.Offset(0, 1).Select 
ActiveCell.FormulaR1C1 = _ 
"=IF(LEFT(RC[1],2)="" "",TRIM(LEFT(RC[1],12)),"""")"

<!-- BEGIN TEMPLATE: bbcode_code --><!-- END TEMPLATE: bbcode_code -->I now need to be able to fill these 2 formulae down for the full block of data which is to the right.

I would have usually used something like:

Dim rng As Range 
Set rng = ActiveSheet.Range("B3") 
If Range("B1048576").End(xlUp).Row > 3 Then 
    rng.AutoFill Range(rng, rng.Offset(0, -1).End(xlDown).Offset(0, 1))

in both columns B and A (the two inserted columns), but I dont want to fix the range to B3 & A3, I want to perform the action based on the active cell that the user starts the macro on as it may not always start on row 3.

Can anyone help?

Thanks

Banjoflanjo

 

[banjoflanjo]

Managed to sort this using:

Dim n As Long
     
    n = Cells(Rows.Count, ActiveCell.Offset(, 2).Column).End(xlUp).Row - ActiveCell.Row + 1
     
    With ActiveCell
        .Resize(n).FormulaR1C1 = "=IF(LEFT(RC[2],2)=""  "",R[-1]C,TRIM(LEFT(RC[2],12)))"
        .Offset(0, 1).Resize(n).FormulaR1C1 = "=IF(LEFT(RC[1],2)=""  "",TRIM(LEFT(RC[1],12)),"""")"
    End With

Thanks

Banjoflanjo