How to find the last non-empty and enter a formula of a range of cells from the next row and on?

[DreyFox]

Hello,

I have Sheet1 with the following table

I need a VBA code that I would put in a button which would find the last non empty cell in the 1st column, which would be A14, then offset by 1 to the next cell below. Then I would like to insert formula from that cell to cell B40.

 

[jasonb75]

Something like this

Sub DreyFox()
Dim lRow As Long, rng As Range

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Set rng = Range(Cells(lRow + 1, 2), "B40")
    Rng.Formula = "your formula goes here"
End Sub

 

[DreyFox]

Something like this

Sub DreyFox()
Dim lRow As Long, rng As Range

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Set rng = Range(Cells(lRow + 1, 2), "B40")
    Rng.Formula = "your formula goes here"
End Sub

Hi Jason,

Thanks for the response. That kind of works, however, I need column A to have the formulas as well. Currently, it seems to only add the formulas to column B. Pleas advise me. Thank you.

Drey

 

[jasonb75]

Easy enough, I just misread the offset ask in your post.

Sub DreyFox()
Dim lRow As Long, rng As Range

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Set rng = Range(Cells(lRow + 1, 1), "B40")
    Rng.Formula = "your formula goes here"
End Sub

 

[DreyFox]

Easy enough, I just misread the offset ask in your post.

Sub DreyFox()
Dim lRow As Long, rng As Range

    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    Set rng = Range(Cells(lRow + 1, 1), "B40")
    Rng.Formula = "your formula goes here"
End Sub

That works as intended. Thank you so much