VBA Help - Set Lastrow based on specific text?

[Johnny Thunder]

Hi guys - working on a project to set some formulas to the lastrow. normally I would use the "Cells(Rows.Count, 1).End(xlUp).Row" type method because it works great for me. But for this worksheet I am unable to use this method so I spotted a column that defines the last row based on a cells text "Grand Total".

Here is my code but it is not working - Not sure of a better way to do this?

Sub BluePivot()


Dim sht         As Worksheet
Dim lastR       As Long


Set sht = Sheets("Pivot Table")
lastR = Cells.Find(What:="Grandtotal", _
                    After:=Range("BD2"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row




With sht


Range("BB4:BB4" & lastR & "").FormulaR1C1 = "=IF(RC[8]=TRUE,MAX(R2C:R[-1]C)+1,"""")"


Range("BC4:BC" & lastR & "").FormulaR1C1 = "=IF(R[-1]C="""", """",IF(R[-1]C[1]=""grandtotal"","""",IF(R[-1]C[1]=""title"",R[-1]C+9,R[-1]C)))"

End With

End sub

 

[Fluff]

You haven't qualified your ranges, try

Sub BluePivot()

Dim lastR       As Long

With Sheets("Pivot Table")
   lastR = .Cells.find(What:="Grandtotal", _
                       After:=Range("BD2"), _
                       LookAt:=xlPart, _
                       lookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
   
   .Range("BB4:BB4" & lastR).FormulaR1C1 = "=IF(RC[8]=TRUE,MAX(R2C:R[-1]C)+1,"""")"
   
   .Range("BC4:BC" & lastR).FormulaR1C1 = "=IF(R[-1]C="""", """",IF(R[-1]C[1]=""grandtotal"","""",IF(R[-1]C[1]=""title"",R[-1]C+9,R[-1]C)))"
End With

End Sub