Loop paste formula until next cell in range is empty

Question

I am trying to paste a formula next to range of cells, but only the one's that contains a value, the script must loop until the next cell in the range is empty. For instance Sheet 1 Column A contains date until row 12, then I would like to paste a formula in column D2:D12 Regards

Answer 1

Like this?

Option Explicit

Sub Sample()
    Dim lastRow As Long, i As Long
    Dim ws As Worksheet

    Set ws = Sheets("Sheet1")

    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    With ws
        For i = 1 To lastRow
            If Len(Trim(.Range("A" & i).Value)) <> 0 Then _
            .Range("D" & i).Formula = "YOUR FORMULA"
        Next i
    End With
End Sub

Answer 2

As you are looking down to the first blank cell then you can avoid a loop and use

The code includes a test to make sure that the code doesn't proceed if all of column A is blank - ie if the range from A1 down extends to the bottom of the sheet and A1 is blank

This code adds a sample formula linking each cell in column D to the respective row in column B

Sub FillData()
    Dim rng1 As Range
    Set rng1 = Range([a1], [a1].End(xlDown))
    If Not (rng1.Rows.Count = Rows.Count And Len([a1].Value) = 0) Then rng1.Offset(0, 3).FormulaR1C1 = "=RC2"
End Sub

Answer 3

I like Sid's beginning, but once you have the range of rows, you can insert the formula into column D all at once, without looping, several ways, here's one:

Option Explicit

Sub AddFormula()
    Dim LR As Long
    LR = Range("A" & Row.Count).End(xlUp).Row
    Range("D2:D12").Formula = "=A2 + 7"  'just an example of a formula
End Sub

Answer 4

Try this:

Range("A:A").SpecialCells(2).Areas(1).Offset(, 3).Formula = "MyFormula"

Answer 5

This is a simple solution that is built into Excel, as long as you don't want to copy to the first blank, jump over the blank, then continue copying:

Enter the formula in the first cell of your range, and as long as it is in the column directly to the right or left of your range of filled cells, simply double-click the black box handler in the bottom right-hand corner of the cell. That will automatically copy your formula down to the last non-empty cell of the range.