Find First Row and Insert Formula to Last Row

Question

When A1 changes, I want to insert a formula to first row and drag it down to last row.
First row is where string "ABC" in column D offset by 2 columns to the right (how do I use offset function instead of just putting 5?)

My try:

Option Explicit
Private Sub WorkSheet_Change(ByVal Target As Range)
Dim firstrow As Long, lastrow As Long
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
       firstrow = .Cells(Application.WorksheetFunction.Match("ABC", .Range("D:D"), 0), 6)
       lastrow = .Cells(fr).End(xlDown).Row
           With Range(firstrow, lastrow)
               .Formula = "=$F$1+G1"
           End With
    End If
End Sub

Obviously this doesn't seems to work...
I know there got to be easier and much clever/simpler ways to do this

Thank you for the help.

Several issues - use `Long` instead of `Integer`, then research `Range` vs `Cell` references - both `Cells(fr)` and `Range(fr, lr)` look wrong. Then use `Option Explicit`. You declared `firstrow` and `lastrow` but then are attempting to use `fr` and `lr`. — BigBen, Aug 03 '19 at 17:57
I fixed the `fr, lr` added `option explicit` and still trying to figure out .range vs .cell. My data is only +-800 is there a reason to use `Long` instead of `Integer`? would `Integer` be faster? — ggmkp, Aug 03 '19 at 18:14
See https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long — BigBen, Aug 03 '19 at 18:24
Thanks for the link, but even after reading 16bit vs 32bits I don't quite get it. It's just because either 32 or 64-bit office is common nowadays use Long (32bits). The formula should go into Column F sorry it should be 6 — ggmkp, Aug 03 '19 at 21:44

score 1 · Accepted Answer · answered Aug 03 '19 at 22:55

I'm not sure exactly which column should be used to determine the lastRow, but something like this should do the trick:

Private Sub WorkSheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub

    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
        Dim firstRow As Variant
        firstRow = Application.Match("ABC", Me.Range("D:D"), 0)

        If Not IsError(firstRow) Then
            Dim lastRow As Long
            lastRow = Me.Cells(Me.Rows.Count, "D").End(xlUp).Row
            Me.Range("F" & firstRow & ":F" & lastRow).Formula = "=$F$1+G1"
        End If
    End If
End Sub

Find First Row and Insert Formula to Last Row

1 Answers1