Find Next Empty Cell in Row

[temerson]

I need a code where:

1. It will find the next empty cell in row 1
2. add text to this empty cell
3. auto fill a formula down to the last non-empty cell

I have something similar below, but it is not dynamic.

Range("L1").FormulaR1C1 = "CASE QTY"
    Range("L1").Select
   
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249977111117893
        .PatternTintAndShade = 0
    End With
    With Selection
        .Font.Bold = True
    End With
    Range("L2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    ActiveCell.FormulaR1C1 = "1"
   
    Selection.AutoFill Destination:=Range("L2:L" & Range("K" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select

 

[rpaulson]

Sub go()

fr = Range("L1").End(xlDown).Row + 1 'first row

Cells(fr, "L").Select
ActiveCell.Value = "CASE QTY"
'.....do other stuff it you an too


lr = Cells(fr, "L").End(xlDown).Row - 1 'last row
Range("L" & fr & ":L" & lr).FillDown

End Sub

hth,

Ross

 

[MARK858]

Maybe...

Sub lastx3()
Dim myRng As Range
    With Cells(1, Rows(1).Find("*", , xlValues, , xlByColumns, xlPrevious).Column).Offset(, 1)
       
        .Value = "CASE QTY"
       
        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With

        .Font.Bold = True
   
Set myRng = .Offset(1)
End With
    With Range(myRng, Cells(Columns(myRng.Column - 1).Find("*", , xlValues, , xlByRows, xlPrevious).Row, myRng.Column))
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        .Borders.Weight = xlThin
        .Value = 1
    End With

End Sub

If you have an actual formula as per your question (you haven't posted a formula, you have posted a string) then change

.Value = 1

to

.Formula = "your formula, doubling up any quotes"