Add formula to last row and use a range B:lastrow in the formula

[Glasgowsmile]

I'm attempting something like the below but I'm having trouble getting it to work.

The B column is filled by vba code so I don't know how many rows it will take up. I want to be able to add a formula in the B column after the last row but this means I can't just do B18:B2000 as the reference for this formula since it gives me a circular reference.

How can I find the last row and use that last row as the end of the range for the B column?

Sub last_row()
Worksheets("ARCS").Activate
Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row

With ActiveSheet
 .Cells(Rows.Count, "C").End(xlUp)(2).Formula = "=IF(COUNTIF(B18:lastrow, ""Yes""), ""New Value"")"
End With
End Sub

 

[Joe4]

See if this does what you want:

Sub MyMacro()

    Dim lastrow As Long

'   Find lastr row in column B with an entry
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Put formula in row after last row in column B
    Cells(lastrow + 1, "B").Formula = "=IF(COUNTIF(B18:B" & lastrow & ", ""Yes""), ""New Value"")"
    
End Sub