Cell formulas with VB

[Mike Fogleman]

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

 

[Vasant Nanavati]

Can VB variables be used in a cell formula?

No.

 

[Frank Kabel]

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

note the multiple apostrophes: ( ' " &

 

[mudraker]

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

 

[Vasant Nanavati]

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

 

[Mike Fogleman]

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.