VBA - find last row and add formula

[GeorgeTimes]

Hi all,

I need your help for a VBA code that does the next:

Find first blank cell in column A (I have this) : Range("A" & Rows.Count).End(xlUp).Offset(1).Select

Once you found the last column from A, I need to populate this formula till last row based on column G : =G4. So I will have:

A10 has value =G10
A13 has value =G13 and so on...


My issue is:

1 - If last row in G column is 50, and the first empty row in column A is 40, then I need a code to paste the formula to A40 - A50. I'm not sure how to write that code in order to paste within this range (the range will be different every time I run the code)

2 - how can I modify the formula from =G4 to =G40, =G41 etc. if I don't know what row will be the last one when I run the macro. The number of row needs to be the same in column A and column G. Again, I'm not sure how I can change the number from the formula as next time I run the code I can start from row 70 for example


I've attached an example of this. A2 and A3 already have the formula, G6 is the last row so I need a vba to fill in from A4 till A6 the next:
A4: =G4
A5: =G5
A6: =G6

 

[DanteAmor]

I forgot to mention, but I need something that will not add the formula to the rest of column A. In A2 and A3, there will be values, not formula

Try this:

Sub PutFormula()
  Dim lr1 As Long, lr2 As Long
  lr1 = Range("A" & Rows.Count).End(3).Row + 1
  lr2 = Range("G" & Rows.Count).End(3).Row
  Range("A" & lr1 & ":A" & lr2).Value = Range("G" & lr1 & ":G" & lr2).Value
End Sub

 

[Alex Blakenburg]

Just updating my previous XLookup post to cater for finding Last Row on both the Active main sheet where the formula goes and Sheet1 where the lookup table is.

Sub FillDownFormulaXlookup()
    '=XLOOKUP(I7,Sheet1!G3:G10,Sheet1!J3:J10,"")
    Dim shtMain As Worksheet
    Dim rng As Range
    Dim lastRowA As Long, lastRowG As Long, lastRowLookup
    
    Set shtMain = ActiveSheet
    
    With shtMain
        lastRowA = .Range("A" & Rows.Count).End(xlUp).Row
        lastRowG = .Range("G" & Rows.Count).End(xlUp).Row
        lastRowLookup = Worksheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row  ' Used in Replace #
        Set rng = .Range(.Cells(lastRowA + 1, "A"), .Cells(lastRowG, "A"))
        rng.Formula = "=XLookup($I" & lastRowA + 1 & Replace(",Sheet1!$G$3:$G$#,Sheet1!$J$3:$J$#,"""")", "#", lastRowLookup)
        rng.Value = rng.Value
    End With
    
End Sub

 

[GeorgeTimes]

Thank you all for your help, I've managed to do this based on your examples.

I will paste the formula below in case anyone needs this in the future (the columns can easily be changed in the below formula, I was more interested in the first part of the formula - getting the row number inside the formula)

Klastrow = ActiveSheet.Cells(Rows.Count, 11).End(xlUp).Offset(1).Row

For k = Klastrow To Ulastrow

ActiveSheet.Range("K" & k).Value = "=XLOOKUP(" & "E" & k & ",$E$4:$E$20000,$K$4:$K$20000,"""")"

 

[Alex Blakenburg]

Thanks for the follow up. Glad we could help.