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

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

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

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