Display result of operation in macro

[orquidea]

Hi All

I have the below subprocedure. I get the numerical right answer when it is
displayed in a selected cell ie Range("A1"), but when I set it to be the
equation of a variable I get "Run Time Error "13" Type Mismatch".

Dim pu As Double
pu = "=COUNTIF(Sheet1!e:e,""*-p*"")"
MsgBox (pu)

Could anyone explain to me why I am getting this error and how can I fix it?

Thanks in advance and Happy Holidays!

Orquidea

 

[Dave Peterson]

You're assigning a string to the pu variable. Just because it looks like a
worksheet formula doesn't mean that VBA will evaluate it.

You could try:

dim pu as long 'why use a double?
pu = application.countif(worksheets("sheet1").range("e:e"), "*-p*")
msgbox pu 'no ()'s required here

 

[orquidea]

Thanks for your help. It worked

You're assigning a string to the pu variable. Just because it looks like a
worksheet formula doesn't mean that VBA will evaluate it.

You could try:

dim pu as long 'why use a double?
pu = application.countif(worksheets("sheet1").range("e:e"), "*-p*")
msgbox pu 'no ()'s required here