[Solved] VBA: Inserting Formulas Via Code

    Hello,
    I'm trying to insert the following formula via code
    =IF('P:\UTP\Candidate Tracking\The Candidate Records\[Bethany VanMaanen.xls]Main Record'!Rnwltr2 = "","",'P:\UTP\Candidate Tracking\The Candidate Records\[Bethany VanMaanen.xls]Main Record'!Rnwltr2)


    But this is what it is placing in the cell:


    =IF('P:\UTP\Candidate Tracking\The Candidate Records\[Bethany VanMaanen.xls]Main Record'!Rnwltr2 = ",",'P:\UTP\Candidate Tracking\The Candidate Records\[Bethany VanMaanen.xls]Main Record'!Rnwltr2)


    Here is the code that I'm using:


    Cells(Cell.Row, 65).Formula = "=IF('P:\UTP\Candidate Tracking\The Candidate Records\[" & myCandidate & ".xls]Main Record'!Rnwltr2 = "","",'P:\UTP\Candidate Tracking\The Candidate Records\[" & myCandidate & ".xls]Main Record'!Rnwltr2)"


    Does anybody have suggestions as to how to make this work?

    Hi -


    When you want to embed the double quote (") in the formula, you have to use CHR(34) or VBA thinks you are trying to close the original string.


    Try something like this - -


    Code
    Cells(Cell.Row, 65).Formula = "=IF('P:\UTP\Candidate Tracking\The Candidate Records\[" & myCandidate & ".xls]Main Record'!Rnwltr2 = " & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & ",'P:\UTP\Candidate Tracking\The Candidate Records\[" & myCandidate & ".xls]Main Record'!Rnwltr2)"


    I think I got all the quotes in the right place, but you get the idea...:wink2:


    .....Ralph

    Ralph thanks for the reply. Shortly after I posted I found that putting quotes around the quotes worked too.
    Like this: Cells. Formula = "IF(A1<>"""",A1*3, 0)". However, I think I may use your method in the future because it seems less confusing to look at.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login