why won't this work???

[Zab]

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

 

[Mike H]

Try this

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

Mike

 

[Zab]

i tried it and it did not work. :-( but hey thanks for the quick response
and nice try.

 

[Office_Novice]

This shoud do it

Sub fourteenftauto()
Dim i As Variant

i = ActiveCell.Offset(0, -7).Value

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

End Sub

 

[XP]

One way:

Dim here As Double
here = ActiveCell.Offset(0, -7).Value
ActiveCell.FormulaArray = "=sumif(b1:b1000," & here & ",h1:h1000)/168"

Notice: Dim as double; FormulaArray

HTH

 

[XP]

Sorry, actually, you don't need "FormulaArray", just "Formula".

 

[Jeff Johnson]

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.

 

[Zab]

thank you all for your efforts!
--
Zab

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.

 

[Zab]

it works!

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