VB code to Run formula untill blank cell

**swinnersmusic** · 05-16-2012, 06:46 PM

Rajest:
also experiment with recording a macro.
here is one that has captured entering a formula and filling it across:

Code:

Sub enterformula()
'
' enterformula Macro
'

'
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=100*RC[-1]"
    Range("E6").Select
    Selection.AutoFill Destination:=Range("E6:L6"), Type:=xlFillDefault
    Range("E6:L6").Select
    Range("F9").Select
End Sub

regards
Alan

**Rick Rothstein** · 05-16-2012, 07:01 PM

Okay, now I think I see what you want. If I am right, then you can do it using this single line of code...

Code:

Range(Range("D27"), Range("D27").End(xlToRight)).Formula = Range("D27").Formula

More generally, where the cell reference could change, this macro...

Code:

Sub ExtendFormulaToRight()
  Dim StartCell As Range
  Set StartCell = Range("D27")
  Range(StartCell, StartCell.End(xlToRight)).Formula = StartCell.Formula
End Sub

Where I assume you might change the starting cell for your extending the formula to the right... just change the Set code line as needed.