Cell formulas with VB
 

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

Cell formulas with VB
 

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


No.


--

Vasant

Cell formulas with VB
 

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

Cell formulas with VB
 

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/

Cell formulas with VB
 

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

Cell formulas with VB
 

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.