assigning value in macro

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Vijay Chary]

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 (e-mail address removed)

 

[Dave Peterson]

Or:

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

 

[CLR]

Thanks Bob.......

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

Vaya con Dios,
Chuck, CABGx3