AutoFill in Selection Range Method

[aejaz7]

Sub demo()

Dim myRange As Range
Set myRange = Selection
myRange.Cells(1, 10).Select
ActiveCell.FormulaR1C1 = "=test" & "(RC[-9])"
Selection.AutoFill Destination:=Range("RC[-9]:RC" & Range("Selection" & Rows.count).End(xlUp).Row), Type:=xlFillDefault

End Sub

A	B	C	D	E	F	G	H	I	J
Text1									#VALUE!​
Text2
Text10
Text1

Autofill in selection, selection is range("A1:A4") in this demo file.
range is selection like range("A1:A4") or may be any then enter formula which depending first cell of selection.Test is function formula.
but i want to autofill test function cell till last cell of selection row.
Is that possible?

Thanks

 

[DanteAmor]

Try this

Sub demo()
  Dim r As Range
  Range("A1:A4").Select 'selection is range("A1:A4") in this demo file.
  Set r = Selection
  r.Offset(, 9).FormulaR1C1 = "=""test"" & RC[-9]"
End Sub

Or this

Sub demo2()
  Dim r As Range
  Range("A1").Select  'one cell
  Set r = Selection
  r.Offset(, 9).FormulaR1C1 = "=""test"" & RC[-9]"
  r.Offset(, 9).AutoFill r.Offset(, 9).Resize(r.Cells(Rows.Count).End(3).Row), xlFillDefault
End Sub

 

[aejaz7]

Try this

Sub demo()
  Dim r As Range
  Range("A1:A4").Select 'selection is range("A1:A4") in this demo file.
  Set r = Selection
  r.Offset(, 9).FormulaR1C1 = "=""test"" & RC[-9]"
End Sub

Or this

Sub demo2()
  Dim r As Range
  Range("A1").Select  'one cell
  Set r = Selection
  r.Offset(, 9).FormulaR1C1 = "=""test"" & RC[-9]"
  r.Offset(, 9).AutoFill r.Offset(, 9).Resize(r.Cells(Rows.Count).End(3).Row), xlFillDefault
End Sub

Range is not fix, it's depend on selection and will be any.
Thanks for your response.

 

[DanteAmor]

Try this

Sub demo()
  Dim r As Range
  Set r = Selection
  r.Offset(, 9).FormulaR1C1 = "=""test"" & RC[-9]"
End Sub

 

[aejaz7]

Try this

Sub demo()
  Dim r As Range
  Set r = Selection
  r.Offset(, 9).FormulaR1C1 = "=""test"" & RC[-9]"
End Sub

Thanks its working without autofill.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.