Solved: Formula property compile error

[viomman]

I know this is a noob question but I am stuck and cant find the answer. Can any ine tell me why i get a compile error in vba using this formula property code.
Selection.Formula = _
"=IF(G2="CONSTRUCTION"," '.600")"

[Aussiebear]

Are you sure you have the formula constructed correctly? Excel requires the following IF (Logical Test,[Value if True],[Value if False]). As you have written it, the formula seems to fail in the value if true/false components.

Did you mean "=IF(G2="Construction","",".600")"

[rbrhodes]

Hi v,

Not so noob. VBA considers "xxx" to be a string so it requires any " inside the string to be dbl quted as well as well.

This will work:

Range("A1") = "=IF(G2=""CONSTRUCTION"","""","".600"")"

Looks confusing but notice that the beginning quote:

"=IF( <snip> and the ending quote <snip> .600)" enclose the complete string.

All the ones in the formula itself are enclosed in their own quotes:

"CONSTRUCTION" becomes ""CONSTRUCTION""

"" becomes """"

and ".600" becomes "".600""

When the formula is read and placed in the cell VBA drops all the extra "" and so the result is what you wanted:

=IF(G2="CONSTRUCTION","",".600")

BTW single ' ' are special too!

[RolfJ]

Thanks, dr, for the detailed explanation on how to deal with strings inside strings. I am sure double-double quote syntax has been bugging generations of both noob as well as seasoned programmers.

I have run across a different approach I thought I should throw into the discussion. It doesn't neccessarily make for more concise code, but reminds you at what you need to be aware of.

I prefer to define a string for the double quote, e.g.

[vba] Dim d As String 'double quote
d = Chr$(34)
[/vba]

Now the formula in this conversation can be written this way:

[vba]"=If(G2=" + d + "Construction" + d + "," + d + "" + d + "," + d + ".600" + d + ")"[/vba]

[MWE]

Thanks, dr, for the detailed explanation on how to deal with strings inside strings. I am sure double-double quote syntax has been bugging generations of both noob as well as seasoned programmers.

I have run across a different approach I thought I should throw into the discussion. It doesn't neccessarily make for more concise code, but reminds you at what you need to be aware of.

I prefer to define a string for the double quote, e.g.

[vba] Dim d As String 'double quote
d = Chr$(34)
[/vba]
Now the formula in this conversation can be written this way:

[vba]"=If(G2=" + d + "Construction" + d + "," + d + "" + d + "," + d + ".600" + d + ")"[/vba]

you should use the ampersand (&) instead of the plus sign (+) when building compound strings. The + operator should be used for numerical "additions". I have found cases where VBA got confused when I used "+" instead of "&" in string/text operations.

[RolfJ]

Yes, yes, you are right, and it's probably not good practice to post a reply using + for string concatenation without qualifying why it would be OK in this case. Me bad!

In general I too use the & character, however in this particular case using it made the whole formula look even more unwieldy.

Now, as you probably know, the problem you are referring to with using the + character has to do with the automatic typecasting VBA employs. IF 'String' is the string representation of a number, but only then, the construct 'String + Number' automatically typecasts the string to a number and returns a number which then gets again automatically typecasted back to a string if that's required in the context. HOWEVER if 'String' is NOT the string representation of a number the construct 'String + Number' returns an error. THEREFORE as long as I only concatenate strings with the + character it IS INDEED equivalent to the & character.

[mdmackillop]

If you have a coomplex workshhet formula, you can easily get the code for it. Select the cell, start the macro recorder, press F2 and Enter. Stop recording. You should then have
[vba]
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveCell.FormulaR1C1 = "=IF(R[-12]C[3]=""CONSTRUCTION"","" '.600"")"
Range("D15").Select
End Sub

[/vba]
which you can then tidy up.

You could also consider a simple function

[vba]
Sub Macro1()
ActiveCell.FormulaR1C1 = "=IF(R[-12]C[3]=" & DQ("Construction") & "," & DQ(".600") & ")"
End Sub

Function DQ(Data As String)
DQ = """" & Data & """"
End Function
[/vba]

[MWE]

I can not think of the number of times the solution to a problem was to turn on the macro recorder, do it manually, turn off the recorder and then see what VBA was generated.

[viomman]

Thanks to all that posted. It really helped. Here is what my code looks l ike for all that is interested. I am going to work on cleaning it up today as per this post but this code work fine also.
[vba]
Sub BudElCodeRes()
With Application
.ScreenUpdating = False
Range("W1").Select
ActiveCell = ("WP_ID")
Do While IsEmpty(ActiveCell.Offset(0, -2)) = False
ActiveCell.Offset(1, 0).Select
Selection.Formula = "=IF(G" & ActiveCell.Row & "=""CONSTRUCTION"","".600"",IF(G" & ActiveCell.Row _
& "=""MISC"","".00"",IF(G" & ActiveCell.Row & "=""ROW"","".300"",IF(G" & ActiveCell.Row & "=""PREDESIGN"", _
"".100"",IF(G" & ActiveCell.Row & "=""DETAIL DESIGN"","".206"",IF(G" & ActiveCell.Row & "=""CONSERV"","".600""))))))"
ActiveCell.Offset(0, -1).Select
Selection.Formula = _
"=CONCATENATE(U" & ActiveCell.Row & ",W" & ActiveCell.Row & ")"
ActiveCell.Offset(0, 1).Select
Loop
.ScreenUpdating = True
End With
End Sub
[/vba]