Programmatically add a Formula in a worksheet?
 

Hi,

My workbook has 2 worksheets: WSA & WSB. WSB gives stats on numbers
shown in WSA. In WSB, in cell B2, there is a function that counts
elements from WSA:

=COUNTIF('WSA'!D2:D35000,"<N/A")

Still in WSB, using a commandButton, I'd like to modify the above
formula programmatically. I've been trying using:


ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula = "=COUNTIF('WSA'!
D2:D36000,"<N/A")'

but no luck. However, if I put something simple that such
as: .Formula = "=sum(D3:D10)", it works. So I suspect the worksheet
referencing is in cause here.

Thanks for your help

Programmatically add a Formula in a worksheet?
 

Hi,

The problem is the quotes, note how i've doubled the internal quotes up
around the NA

ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula =
"=COUNTIF('WSA'!D2:D36000,""<N/A"")"

Mike

"circuit_breaker" wrote:

Hi,

My workbook has 2 worksheets: WSA & WSB. WSB gives stats on numbers
shown in WSA. In WSB, in cell B2, there is a function that counts
elements from WSA:

=COUNTIF('WSA'!D2:D35000,"<N/A")

Still in WSB, using a commandButton, I'd like to modify the above
formula programmatically. I've been trying using:


ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula = "=COUNTIF('WSA'!
D2:D36000,"<N/A")'

but no luck. However, if I put something simple that such
as: .Formula = "=sum(D3:D10)", it works. So I suspect the worksheet
referencing is in cause here.

Thanks for your help

Programmatically add a Formula in a worksheet?
 

Sub doformula()
ActiveWorkbook.Sheets("wsb").Cells(1, 2).Formula = _
"=COUNTIF('wsa'!D2:D36000,""<N/A"")"

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"circuit_breaker" wrote in message
...
Hi,

My workbook has 2 worksheets: WSA & WSB. WSB gives stats on numbers
shown in WSA. In WSB, in cell B2, there is a function that counts
elements from WSA:

=COUNTIF('WSA'!D2:D35000,"<N/A")

Still in WSB, using a commandButton, I'd like to modify the above
formula programmatically. I've been trying using:


ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula = "=COUNTIF('WSA'!
D2:D36000,"<N/A")'

but no luck. However, if I put something simple that such
as: .Formula = "=sum(D3:D10)", it works. So I suspect the worksheet
referencing is in cause here.

Thanks for your help

Programmatically add a Formula in a worksheet?
 

That simple! It works, thanks very much.
On Jul 6, 9:17*am, circuit_breaker wrote:
Hi,

My workbook has 2 worksheets: WSA & WSB. *WSB gives stats on numbers
shown in WSA. *In WSB, in cell B2, there is a function that counts
elements from WSA:

=COUNTIF('WSA'!D2:D35000,"<N/A")

Still in WSB, using a commandButton, I'd like to modify the above
formula programmatically. *I've been trying using:

ActiveWorkbook.Sheets("WSB").Cells(1, 2).Formula = "=COUNTIF('WSA'!
D2:D36000,"<N/A")'

but no luck. *However, if I put something simple that such
as: *.Formula = "=sum(D3:D10)", *it works. *So I suspect the worksheet
referencing is in cause here.

Thanks for your help