PDA

View Full Version : [SOLVED] setting a cell to a formula



siobhan86
12-31-2019, 09:39 AM
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
12-31-2019, 09:52 AM
"=AVERAGE(H21:"& lastsal ")"
Almost there
"=AVERAGE(H21:"& lastsal & ")"

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

siobhan86
12-31-2019, 10:00 AM
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
12-31-2019, 10:14 AM
Hi again, you can disregard this problem as I figured out what was wrong. Thank you so much for your help!