How to pass the variable in excel formula in macro?

[Rampage598]

Sub test1()
Sheets("FinalData").Select
lastCol = Split(Cells(2, Columns.Count).End(xlToLeft).Address, "$")(1)
Range("A2").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Running"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Cycling"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Strength(Mins)"
ActiveCell.Offset(1, 0).Formula = "=Sumif($B$2:" & lastCol & "$2," ""*Strength(Mins)*""",B3:" & lastCol & "3)"
End Sub

This is my code ,
created following variable into it

lastCol = Split(Cells(2, Columns.Count).End(xlToLeft).Address, "$")(1)

want to pass into the Formula...

ActiveCell.Offset(1, 0).Formula = "=Sumif($B$2:" & lastCol & "$2," ""*Strength(Mins)*""",B3:" & lastCol & "3)"

but its giving me an error....

Can anyone help me with this?

 

[Fluff]

Hi & welcome to MrExcel.
How about

ActiveCell.Offset(1, 0).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Strength(Mins)*"",B3:" & lastCol & "3)"

 

[Rampage598]

Hi & welcome to MrExcel.
How about

ActiveCell.Offset(1, 0).Formula = "=Sumif($B$2:" & lastCol & "$2,""*Strength(Mins)*"",B3:" & lastCol & "3)"

Thanks , its working

 

[Fluff]

You're welcome & thanks for the feedback.