help with inserting variables into formula

[jordanburch]

Hey guys

I have the below. It is erroring out on the second statement. I suspect a quote is in the wrong spot. Can someone please help? ALso, can someone explain to me when to use "" and """ and & when using variables within formulas? it doesnt make sense to me.

Jordan

ActiveCell.FormulaR1C1 = _
"=+IF(AND(RC[-7]=""=CO21army" & Left(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 3) & _
Right(ThisWorkbook.Worksheets("Variables").Range("A3").Value, 2) & ".xlsx"",OR(RC[-12]="" _
& right(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 2) & "/" & left(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 4)", RC[-12]="" & right(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 1) & "/" & left(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 4)"")),""X"","" "")"


", RC[-12]="" & right(ThisWorkbook.Worksheets("

 

[offthelip]

The best way i have found for sorting these problems is to remove the equals sign at the start so that you are just writing text to the cell instead of an equation, Then selelct the cell and put the equals sign back in again, excel will then show you where the error is. i.e change:

ActiveCell.FormulaR1C1 = _
"=+IF(AND(RC[-7]=""=CO21army" & Left(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 3) & _

to

ActiveCell.value = _
"+IF(AND(RC[-7]=""=CO21army" & Left(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 3) & _

the double quote character is used in VBA to show the start and end of a text string, this is usually fairly obvious until you wnt to include the double quote character into the text string. it is in the this second case that you need the """. I always avoid this by using the ascii function to create the double quote character SO if I want string that was "Varaibles" i.e including double quotes
is would create as :

tt = Chr(34) & "Variables" & Chr(34)

compare this to what you get with :

tt = """Variables"""

 

[jordanburch]

The best way i have found for sorting these problems is to remove the equals sign at the start so that you are just writing text to the cell instead of an equation, Then selelct the cell and put the equals sign back in again, excel will then show you where the error is. i.e change:

ActiveCell.FormulaR1C1 = _
"=+IF(AND(RC[-7]=""=CO21army" & Left(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 3) & _

to

ActiveCell.value = _
"+IF(AND(RC[-7]=""=CO21army" & Left(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 3) & _

the double quote character is used in VBA to show the start and end of a text string, this is usually fairly obvious until you wnt to include the double quote character into the text string. it is in the this second case that you need the """. I always avoid this by using the ascii function to create the double quote character SO if I want string that was "Varaibles" i.e including double quotes
is would create as :

tt = Chr(34) & "Variables" & Chr(34)

compare this to what you get with :

tt = """Variables"""

thanks this got me going. I found out that if its a string then you need to use :""string""
if its a variable then use "&variable&"
that was the issue here is the corrected code

ActiveCell.Formula = _
"+IF(AND(RC[-7]=""=CO21army" & Left(ThisWorkbook.Worksheets("Variables").Range("a2").Value, 3) & _
Right(ThisWorkbook.Worksheets("Variables").Range("A3").Value, 2) & ".xlsx"",OR(RC[-12]=""" _
& Right(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 2) & "/" & Left(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 4) & ", RC[-12]=" & Right(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 1) & "/" & Left(ThisWorkbook.Worksheets("Variables").Range("a6").Value, 4) & ",""X"","" "")"
Range("AB2").Select