Dynamic Autofill VBA

[upendra2206]

This is a part of my code:

The purpose is to go to the first empty cells in the row 2 and row 3 and then type specific formulas and then autofill in that particular column.(here from L3 to L13)

Sub AutofillTable1()

'go the first empty cell in the 2nd row and type Median
Range("IV2").End(xlToLeft).Offset(0, 1).Select 'go the first empty cell in the 2nd row and type Median
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Median"

'go the first empty cell in the 3nd row and type the formula
Range("IV3").End(xlToLeft).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(RC[-10]:RC[-2],0.5)"

'Autofill the formula from the last filled cell in 3rd row i.e. L3 to the last filled row as per A3.
Range("IV3").End(xlToLeft).AutoFill destination:=Range("L3:L" & Range("A3").End(xlDown).Row)

End Sub

The problem is that I want to make the bold part in the code to be dynamic. i.e it may not always be L column. It may vary.

Also, There is another table with the same format and formula but different references. Now I want to carry out the same operation i.e. go to the first empty cells in the row 24 and row 25 and then type specific formulas and then auto fill in that particular column.(here from L25 to L35)

Sub AutofillTable1()

'go the first empty cell in the 24th row and type Median
Range("IV24").End(xlToLeft).Offset(0, 1).Select 'go the first empty cell in the 2nd row and type Median
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Median"

'go the first empty cell in the 3nd row and type the formula
Range("IV25").End(xlToLeft).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=PERCENTILE(RC[-10]:RC[-2],0.5)"

'Autofill the formula from the last filled cell in 25th row i.e. L25 to the last filled row as per A24.
Range("IV25").End(xlToLeft).AutoFill destination:=Range("L25:L" & Range("A25").End(xlDown).Row)

End Sub

Can Replace the Column Reference i.e. L to a dynamic reference?

Thanks in Advance

 

[Peter_SSs]

For the first part, try this. If it does what you want, the other part should be similar.

Sub Fill_Formulas()
  With Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
    .Value = "Median"
    .Offset(1).Resize(Range("A3").End(xlDown).Row - .Row).FormulaR1C1 = "=PERCENTILE(RC[-10]:RC[-2],0.5)"
  End With
End Sub

 

[upendra2206]

For the first part, try this. If it does what you want, the other part should be similar.

Sub Fill_Formulas()
  With Cells(2, Columns.Count).End(xlToLeft).Offset(, 1)
    .Value = "Median"
    .Offset(1).Resize(Range("A3").End(xlDown).Row - .Row).FormulaR1C1 = "=PERCENTILE(RC[-10]:RC[-2],0.5)"
  End With
End Sub

Perfecto !! Thanks a lot. One more problem.. How can I cut the 2nd table i.e. starting from the 25th row (for eg. A25:I35 in this case) and paste it to the second empty row i.e. if my first table ends at 10th row I want to paste the 2nd table from 11th row.

I have a code for that but the problem is that when I ask the macro to look for the first empty row it takes the first empty row after the second table whereas the rows from 11th to 24th are empty

 

[Peter_SSs]

How can I cut the 2nd table i.e. starting from the 25th row (for eg. A25:I35 in this case) and paste it to the second empty row i.e. if my first table ends at 10th row I want to paste the 2nd table from 11th row.

Possibly this? Test in a copy of your workbook.

Sub MoveTable()
  Range("A25").CurrentRegion.Cut Destination:=Range("A25").End(xlUp).Offset(1)
End Sub

 

[upendra2206]

Exactly what I wanted. Thanks alot..

 

[Peter_SSs]

Glad they both worked for you.