setting a cell to a formula

[siobhan86]

Hi, I am trying to make a cell equal to a formula in VBA using a variable for part of the range. I've tried the following two ways to do it and both fail.

Range("H" & endsal + 2).Select
Dim lastsal As String
lastsal = Selection.Address
MsgBox (lastsal)
ActiveCell.Formula = "=AVERAGE(H21:& lastsal)"

AND
Range("H" & endsal + 2).Select
Dim lastsal As String
lastsal = Selection.Address
MsgBox (lastsal)
ActiveCell.Formula = "=AVERAGE(H21:"& lastsal ")"

Can you help me in getting this to work?

[SamT]

"=AVERAGE(H21:"& lastsal ")"
Almost there
"=AVERAGE(H21:"& lastsal & ")"

Also
"=AVERAGE(H21:H" & endsal + 2 & ")"

[siobhan86]

Thank you so much! That got rid of the error but the formula is not giving me the result I need. it's just giving me a - . Any ideas? the range is correct; each cell has a dollar amount in it.

[siobhan86]

Hi again, you can disregard this problem as I figured out what was wrong. Thank you so much for your help!