why won't this work???
 

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
--
Thank You in advance, Zab

why won't this work???
 

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" wrote:

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
--
Thank You in advance, Zab

why won't this work???
 

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


"Mike H" wrote:

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" wrote:

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
--
Thank You in advance, Zab

why won't this work???
 

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




"Zab" wrote:

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


"Mike H" wrote:

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" wrote:

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
--
Thank You in advance, Zab

why won't this work???
 

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

"Office_Novice" wrote:

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




"Zab" wrote:

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


"Mike H" wrote:

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" wrote:

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
--
Thank You in advance, Zab

why won't this work???
 

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

"XP" wrote:

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

"Office_Novice" wrote:

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




"Zab" wrote:

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


"Mike H" wrote:

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" wrote:

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
--
Thank You in advance, Zab

why won't this work???
 

"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.

why won't this work???
 

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.

why won't this work???
 

it works!

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


"Zab" wrote:

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.