VBA Excel Formula Quotationmarks

1
votes

Hi i am having some trouble with VBA. Im trying to write a formula in a cell like this

ActiveWorkbook.Sheets(1).range("I3").Formula = "=IF(H3<TODAY();" & Chr(34) & "closed" & Chr(34) & ";IF(G3>TODAY();" & Chr(34) & "open" & Chr(34) & ";" & Chr(34) & "in stock" & Chr(34) & "))"

I think the problem lies with the quotationmarks. It is giving me the Error message Run-time error '1004': Application-defined or object-defined error. Thank you in advance

1
vbaexcel
replace the ; with , vba is american english format. - Scott Craner
remove the Chr(34) and change the ; with ,. - Vityata
Change to , instead of ; as a list delimiter or use .FormulaLocal. VBA is very EN-US-centric. - user4039065
escape double quotes by doubling up like so debug.print "=IF(H3<TODAY();""closed"";IF(G3>TODAY();""open"";""in stock""))" .... print this ...=IF(H3<TODAY();"closed";IF(G3>TODAY();"open";"in stock")) - jsotola
Thank you all for the help! - Ivan Filipović

1 Answers

0
votes

Just to summarize all that has been said and to provide a working solution:

ActiveWorkbook.Sheets(1).range("I3").Formula = "=IF(H3<TODAY()," & """closed""" & ",IF(G3>TODAY()," & """open""" & "," & """in stock""" & "))"

Changes:

  1. exchange the ; with , (as noted by @ScottCraner) since VBA formulas have to be entered in American English format.
  2. remove the Chr(34) and instead use two " inside the strings to insert one " effectively when the formula is written by VBA. Since strings themselves start with a " this results each time in three consecutive ".

Update:

(as proposed in the comment)

ActiveWorkbook.Sheets(1).range("I3").Formula = "=IF(H3<TODAY(),""closed"",IF(G3>TODAY(),""open"",""in stock""))"