How can I place a formula in the first empty cell on Column F?

Question

How can I place a formula in the first empty cell on Column F?

F3 is empty cell. Need for that empty cell be =F2

Note: I'm looking for code to look for first empty cell F and I need to be able to insert in the first empty cell =F3.

Currently working with following code copied from here

Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

sourceCol = 6   'column F has a value of 6
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

'for every row, find the first blank cell and select it
For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol).Select
        Exit For 'This is missing...
    End If
Next

Answer 1

You'll need to change your rowCount, the way you have it, the loop will stop before the first blank row. I believe you should just be able to set use .Formula for the empty cell. Hope this helps:

Sub EmptyCellFillFormula()

    Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String

    sourceCol = 6   'column F has a value of 6
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row + 1

    For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, sourceCol).Value
        If IsEmpty(currentRowValue) Or currentRowValue = "" Then
            Cells(currentRow, sourceCol).Formula = "=F3"
        End If
    Next
End Sub

Answer 2

Your existing code implies you want to consider truely Empty cells and cells that contain an empty string (or a formula that returns an empty string) ^{Note 1}. (Given you simply copied that code from elsewhere, that may not be the case)

You can use End(xlDown) to locate the first truely Empty cell, or Match to locate the first "Empty" cell in a range (either just empty string, or either empty strings or Empty cells, in different forms)

If you want to find the first truely Empty cell, or cell containing an empty string:

Function FindFirstEmptyOrBlankCell(StartingAt As Range) As Range
    Dim rng As Range

    'Set search range
    With StartingAt.Worksheet
        Set rng = .Range(StartingAt, .Cells(.Rows.Count, StartingAt.Column).End(xlUp).Offset(1, 0))
    End With

    ' Find first empty or blank cell
    Set FindFirstEmptyOrBlankCell = rng.Cells(StartingAt.Worksheet.Evaluate("Match(True, " & rng.Address & "=""""" & ", 0)"), 1)
End Function

If you want to find the first truely Empty cell, and ignore cells containing an empty string:

Function FindFirstEmptyCell(StartingAt As Range) As Range
    Dim rng As Range

    'Set search range
    With StartingAt.Worksheet
        Set rng = .Range(StartingAt, .Cells(.Rows.Count, StartingAt.Column).End(xlUp).Offset(1, 0))
    End With

    ' Find first empty cell
    If IsEmpty(StartingAt.Cells(1, 1)) Then
        Set FindFirstEmptyCell = rng.Cells(1, 1)
    ElseIf IsEmpty(StartingAt.Cells(2, 1)) Then
        Set FindFirstEmptyCell = rng.Cells(2, 1)
    Else
        Set FindFirstEmptyCell = rng.End(xlDown).Cells(2, 1)
    End If
End Function

And for completeness, if you want to find the fisrt cell containing an empty string, and ignore truely Empty cells:

Function FindFirstBlankCell(StartingAt As Range) As Range
    Dim rng As Range
    Dim idx As Variant
    'Set search range
    With StartingAt.Worksheet
        Set rng = .Range(StartingAt, .Cells(.Rows.Count, StartingAt.Column).End(xlUp).Offset(1, 0))
    End With

    ' Find first blank cell
    idx = Application.Match(vbNullString, rng, 0)
    If IsError(idx) Then
        'There are no Blank cells in the range. Add to end instead
        Set FindFirstBlankCell = rng.Cells(rng.Rows.Count, 1)
    Else
        Set FindFirstBlankCell = rng.Cells(idx, 1)
    End If
End Function

In all cases, call like this

Sub Demo()
    Dim ws As Worksheet
    Dim r As Range

    Set ws = ActiveSheet '<~~~ or specify required sheet
    Set r = FindFirstEmptyOrBlankCell(ws.Range("F3"))

    ' literally what was asked for
    'r.Formula = "=F3"

    ' possibly what was actually wanted
    r.Formula = "=" & r.Offset(-1, 0).Address(0, 0)

End Sub

---

_{Note 1 If IsEmpty(currentRowValue) Or currentRowValue = "" Then is actually redundant. Any value that returns TRUE for IsEmpty(currentRowValue) will also return TRUE of currentRowValue = "" (The reverse does not apply)}

From comment can that same Fuction repeat until the last empty cel? I think this is what you mean is to continue to fill blank cells down through the used range

If so, try this

Sub Demo()
    Dim ws As Worksheet
    Dim cl As Range
    Dim r As Range

    Set ws = ActiveSheet '<~~~ or specify required sheet

    Set cl = ws.Range("F3")
    Do
        Set r = FindFirstEmptyOrBlankCell(cl)
        If r Is Nothing Then Exit Do
        r.Formula = "=" & r.Offset(-1, 0).Address(0, 0)
        Set cl = r.Offset(1, 0)
    Loop
End Sub

Note, I've modified FindFirstEmptyOrBlankCell above to aloow it to return Nothing when it needs to:

Function FindFirstEmptyOrBlankCell(StartingAt As Range) As Range
    Dim rng As Range

    'Set search range
    With StartingAt.Worksheet
        Set rng = .Range(StartingAt, .Cells(.Rows.Count, StartingAt.Column).End(xlUp).Offset(1, 0))
    End With

    ' Find first empty or blank cell
    On Error Resume Next ' Allow function to return Nothing
    Set FindFirstEmptyOrBlankCell = rng.Cells(StartingAt.Worksheet.Evaluate("Match(True, " & rng.Address & "=""""" & ", 0)"), 1)
End Function