| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to update a column in a work based on another work sheet | New Users to Excel | |||
| flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
| HOW TO MAKE A LIST OF WORK SHEET IN WORK BOOK IN EXCEL 2007 | Excel Programming | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Linear Mode
