Hi
When I want a value for of a formula for my macro I write the formula to go
in a blank cell eg range("a1") ="= countif("b:b",b1)" then use myvalue =
range("a1") how do I assign countif to myvalue without doing this?
Thanks
Tina

myValue = Application.COUNTIF(Range("B:B"),Range("B1"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tina" wrote in message
...
Hi
When I want a value for of a formula for my macro I write the formula to
go
in a blank cell eg range("a1") ="= countif("b:b",b1)" then use myvalue =
range("a1") how do I assign countif to myvalue without doing this?
Thanks
Tina

Hi Tina !! :)
Try this. It should work.

range("a1") = chr(34) & "=countif(" & chr(34) &"b:b" & chr(34)& ",b1)"&
chr(34)
Could you let me know if it solves your problem ?
My e-mail ID is

"tina" wrote:

Hi
When I want a value for of a formula for my macro I write the formula to go
in a blank cell eg range("a1") ="= countif("b:b",b1)" then use myvalue =
range("a1") how do I assign countif to myvalue without doing this?
Thanks
Tina

Or:

dim myVal as long
with activesheet
myval = application.countif(.range("b:b"),.range("b1").val ue)
end with
msgbox myval


Vijay Chary wrote:

Hi Tina !! :)
Try this. It should work.

range("a1") = chr(34) & "=countif(" & chr(34) &"b:b" & chr(34)& ",b1)"&
chr(34)
Could you let me know if it solves your problem ?
My e-mail ID is

"tina" wrote:

Hi
When I want a value for of a formula for my macro I write the formula to go
in a blank cell eg range("a1") ="= countif("b:b",b1)" then use myvalue =
range("a1") how do I assign countif to myvalue without doing this?
Thanks
Tina

--

Dave Peterson

Thanks Bob.......

Your suggestion helped me TODAY, 11/29/09!!.......4 1/2 years after you
posted it!

Vaya con Dios,
Chuck, CABGx3





"Bob Phillips" wrote:


myValue = Application.COUNTIF(Range("B:B"),Range("B1"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"tina" wrote in message
...
Hi
When I want a value for of a formula for my macro I write the formula to
go
in a blank cell eg range("a1") ="= countif("b:b",b1)" then use myvalue =
range("a1") how do I assign countif to myvalue without doing this?
Thanks
Tina