thank you all for your efforts!
--
Zab


"Jeff Johnson" wrote:

"Zab" wrote in message
...

i have a macro that won't insert the value from the offset. it just inserts
the word "here". i need the value from "here"

Sub fourteenftauto()
Dim here As String
here = ActiveCell.Offset(0, -7).Value
ActiveCell = "=sumif(b1:b1000,here,h1:h1000)/168"
End Sub

That's because "here" only exists inside the VBA code in your macro. Once
the formula is placed into the worksheet Excel doesn't know that "here" is a
String which contains a value. It's a common misconception I've seen with
people generating SQL statements as well. You have to put the VALUE of the
"here" variable into the formula, like this:

ActiveCell = "=sumif(b1:b1000," & here & ",h1:h1000)/168"

Of course, this puts a static value into your formula. If you want it to
actually reference another cell (the one 7 cells to the left of the active
cell, in your example), then you'll need to use the address of that cell,
not its value.