Find a Column ("name") and Fill down with Formula

Question

Sheets("DATA").Rows(2).Find(What:="Apple", LookIn:=xlValues, _
                                  LookAt:=xlWhole).Offset(1, 0).Value = "=A3-B3"
                                  Selection.FillDown

I want to find a column "Apple" in Row 2 and filldown with formula "A3-B3"
Would something like .value="=A3-B3".filldown work?
Thanks!

No. Try this `.Formula = "=A3-B3"` However You don't need `.FillDown` Let me post a complete example. — Siddharth Rout, Sep 16 '13 at 17:00
Thanks but I am looking for ways to combine `selection` with `.value or .formula` ... and fill the formula down to last row — ggmkp, Sep 16 '13 at 17:03
You shouldn't be using `Selection` Please see [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select/10718179#10718179) I am posting a better solution. — Siddharth Rout, Sep 16 '13 at 17:06

score 2 · Accepted Answer · answered Sep 16 '13 at 17:09

Further to my comments above, try this. I have commented the code. Do let me know if you find anything confusing...

Sub Sample()
    Dim ws As Worksheet
    Dim LRow As Long
    Dim aCell As Range

    '~~> Set this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Data")

    With ws
        '~~> Find Last Row
        LRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Find the cell which has "Apple"
        Set aCell = .Rows(2).Find(What:="Apple", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        '~~> if found, then enter the formula till the last row IN ONE GO
        If Not aCell Is Nothing Then
            .Range(.Cells(3, aCell.Column), .Cells(LRow, aCell.Column)).Formula = "=A3-B3"
        End If
    End With
End Sub

Hope I can get on your level... Thank you for the guidance! – ggmkp Sep 16 '13 at 17:17 — ggmkp, Sep 16 '13 at 17:17
Glad to be of help :) – Siddharth Rout Sep 16 '13 at 17:33 — Siddharth Rout, Sep 16 '13 at 17:33

Find a Column ("name") and Fill down with Formula

1 Answers1