Can VB variables be used in a cell formula? For example I have columns of
data on one sheet and a list to compare to on another. I can put a formula
in a cell and copy down the comparison list to get what I want. This
comparison list remains constant and part of the workbook.
I can write this formula to a range of cells next to the comparison list
from VB:

Range("A2:A90").FormulaArray =
"=SUM((Dan!$E$1:$E$504="""")*(Dan!$B$1:$B$504=A2)) "

In this case a database named Dan.dbf was queried and brought in on a new
sheet which I named "Dan". Dan has 504 rows of data. This query routine
needs to work on any file queried, so I have assigned variables to the sheet
name and the rowcount
nwsht = "Dan"
rowcount = 504

How can I substitute these 2 variables into the above formula? I have played
with syntax variations of all I can think of.
Any advice will be greatly appreciated. Thanks, Mike

"Mike Fogleman" wrote in message
...
Can VB variables be used in a cell formula?


No.


--

Vasant

Hi Mike
try
Range("A2:A90").FormulaArray ="=SUM(('" & nwsht &"'!$E$1:$E$" &
rowcount & "="""")*('" & nwsht &"'!$B$1:$B$" & rowcount & "=A2))"

note the multiple apostrophes: ( ' " &
--
Regards
Frank Kabel
Frankfurt, Germany

Mike Fogleman wrote:
Can VB variables be used in a cell formula? For example I have
columns of data on one sheet and a list to compare to on another. I
can put a formula in a cell and copy down the comparison list to get
what I want. This comparison list remains constant and part of the
workbook.
I can write this formula to a range of cells next to the comparison
list from VB:

Range("A2:A90").FormulaArray =
"=SUM((Dan!$E$1:$E$504="""")*(Dan!$B$1:$B$504=A2)) "

In this case a database named Dan.dbf was queried and brought in on a
new sheet which I named "Dan". Dan has 504 rows of data. This query
routine needs to work on any file queried, so I have assigned
variables to the sheet name and the rowcount
nwsht = "Dan"
rowcount = 504

How can I substitute these 2 variables into the above formula? I have
played with syntax variations of all I can think of.
Any advice will be greatly appreciated. Thanks, Mike

Yes



Dim i As Integer
Dim s As String

s = "Dan"
i = 504
Range("A2:A90").FormulaArray = "=SUM((" & s & "!$E$1:$E$" & i &
"="""")*(" & s & "!$B$1:$B$" & i & "=A2))"


Also your formula has a circular reference to A2


---
Message posted from http://www.ExcelForum.com/

Sorry, I misunderstood your question ... didn't scroll down far enough!

--

Vasant

"Mike Fogleman" wrote in message
...
Can VB variables be used in a cell formula? For example I have columns of
data on one sheet and a list to compare to on another. I can put a formula
in a cell and copy down the comparison list to get what I want. This
comparison list remains constant and part of the workbook.
I can write this formula to a range of cells next to the comparison list
from VB:

Range("A2:A90").FormulaArray =
"=SUM((Dan!$E$1:$E$504="""")*(Dan!$B$1:$B$504=A2)) "

In this case a database named Dan.dbf was queried and brought in on a new
sheet which I named "Dan". Dan has 504 rows of data. This query routine
needs to work on any file queried, so I have assigned variables to the
sheet
name and the rowcount
nwsht = "Dan"
rowcount = 504

How can I substitute these 2 variables into the above formula? I have
played
with syntax variations of all I can think of.
Any advice will be greatly appreciated. Thanks, Mike

Thanks for the responses, guys. Frank was dead on with this one. Mudracker
was as close as I had gotten, but the single quotes had eluded both of us.
The circular ref to A2: A90 was a brain fart in writing the example formula,
B2:B90 would be a more correct example.
Thanks again guys, another good answer for my PERSONAL.XLS.