To write formulas in cells using VBA

[Hari]

Hi,

Is it possible to put a formula in a worksheet through VBA.

I have 2 cells Q3 and Q4 where I want to have formulas --> =IF(D3<>0,D3,"")
and =IF(COUNTIF($Q$3:$Q3,D4)=0,IF(D4="","",D4),"") respectively.

I want to write these formulas thru VB not actually do it beforehand in
Excel because im fearing that somebody may delete these formulas by mistake.
Actually im doing couple of other operations based on these 2 cells hence
wanted to know the syntax for introducing the formulas while the code is
running

Please guide me.

Regards,
Hari
India

 

[Frank Kabel]

Hi
why not protect your sheet to prevent changing the formulas?

But if you want VBA try
ActiveCell.Formula="=IF(D3<>0,D3,"""")"

and
ActiveCell.Formula="=IF(COUNTIF($Q$3:$Q3,D4)=0,IF(D4="""","""",D4),""""
)"

 

[Hari]

Hi Frank,

Thnx a lot for those syntaxes. (I hope im using the correct semantics.)

I might protect the sheet but I run in to lots of problems with selective
protection ( only some cells protected) of a worksheet.

Regards,
Hari
India

 

[Hari]

Hi Frank,

Due to some changes I will need the same formula in cell AT 4 and AT 5
rather than the active cell

I tried :-

Range("at4").Formula = "=IF(D3<>0,D3,"")"
Range("at5").Formula = "=IF(COUNTIF($Q$3:$Q3,D4)=0,IF(D4="","",D4),"")"

But Im getting Run time error 1004. Application-defined or object-defined
error.

Please help me.

Regards,
Hari
India

 

[JE McGimpsey]

Works fine for me when I double the quote marks inside the outer quotes:

Range("at4").Formula = "=IF(D3<>0,D3,"""")"
Range("at5").Formula =
"=IF(COUNTIF($Q$3:$Q3,D4)=0,IF(D4="""","""",D4),"""")"

 

[Frank Kabel]

Hi
if you nee " within a formula you have to enter a double ""

That is try:
Range("at4").Formula = "=IF(D3<>0,D3,"""")"
Range("at5").Formula =
"=IF(COUNTIF($Q$3:$Q3,D4)=0,IF(D4="""","""",D4),"""")"

 

[Hari]

Hi Frank and JEM,

Why do we need 4 double quotes in succession when 2 is the one we normally
use in Excel.

Partly I understand now, that if we put only 2 then excel understands that
my formula finishes at 2nd double quote itself.

How does adding 2 more double quotes circumvent that problem.

Regards,
Hari
India

 

[Frank Kabel]

Hi
if you want to have a " within a string you have to double it. So you
use
""
instead.

If you want to have
""
within a cell you have to double each of these apostrophes (and that
makes 4)