Glasgowsmile
Active Member
- Joined
- Apr 14, 2018
- Messages
- 280
- Office Version
- 365
- Platform
- Windows
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?
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?
VBA Code:
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