CODEWITHSUNDEEP
excelvba
ggmkp
ggmkp

Reputation: 725

Find First Row and Insert Formula to Last Row

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.

Upvotes: 0

Views: 341

Answers (1)

BigBen
BigBen

Reputation: 49998

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

Upvotes: 1

Related Questions