countif blank

[itlsbu]

hello,

I have a macro containing this code
Range("a4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[1]C:R[" & x & "]C," & y & ")"

Where
x = 6
and y = " "

this should result in de formula countif(a5:a10;"") however it doen't

How do I add a blank to this kind of string?

thanx
steven

 

[hatman]

Well, the first tthing I would like to point out is that your message states that y = " "... or, y = [space]. Not a blank, as you state.

another way of getting the blank you expect into the formula might be

Range("a4").FormulaR1C1 = "=COUNTIF(R[1]C:R[" & x & "]C," & chr(34) & chr(34) & ")"

Though another way is to simply use the appropriate number of quotes to force-feed VBA to get them into the formula (not a big fan of it myself, so I will leave that syntax to others).

Also note that you not need to select a cell to manipulate it... observe the difference between my syntax and yours.

 

[itlsbu]

thank yout very much
worked as expected

I think a tried a zillion ways of putting those quotes in, it never worked...

ps I realize my code can be a lot shorter (more efficient, faster), but what starts as a simple 4-line code, becomes a 4 kilo encyclopedia once a start adding a another great routine...

thanks and cheers Steven