compile error expected list separator or )

Question

Hi experts,

Could anyone please help to get the resolve the below,  

while i am trying the below code it shows an syntax error : "compile error expected list separator or )" 

Range("BB2:BB" & Range("AY" & Rows.Count).End(xlUp).Row).Formula = "IF(RC[-37]<>"","VLOOKUP(CONCATENATE(RC[-37]&RC[-46]),'[Formatting_Template.xls]PO Pymt Terms'!C1:C4,4,0)","VLOOKUP(CONCATENATE(RC[-37]&RC[-44]),'[Formatting_Template.xls]Payment Terms & Methods'!C1:C5,5,0)""

Answer 1

Sub Example_HowToCreateAFormulaWithVBA()

  'Copy the formula from Excel as is

  '  =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&"Whatever"

  'double each " sign inside:

  '  =INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever""

  'surround the whole formula with " signs

  '  "=INDEX(C1:C2,MATCH($A$1,B1:B2,0))&""Whatever"""

  'Replace the cell references with a "placeholder" (any unique string that you like and doesn't exists elsewhere)

  '  "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

  'and assign to a string:

  Dim MyDefFormula As String

  MyDefFormula = "=INDEX(#C#,MATCH(#A#,#B#,0))&""Whatever"""

  'Now create the Range objects and refer to the real cells

  Dim A As Range, B As Range, C As Range

  Set A = Range("A1")                                    'A single cell

  Set B = Range("B2:B50")                                'Multiple cells

  Set C = Range("C2", Range("C" & Rows.Count).End(xlUp)) 'From C2 to last used cell in column C

  'Note:

  '  You can refer to cells in an other sheet (note the dot in front of Range!):

  'With Worksheets("That")

  '  Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

  'End With

  '  Or other already opened files:

  'With Workbooks("This.xlsx").Worksheets("That")

  '  Set C = .Range("C2", .Range("C" & Rows.Count).End(xlUp))

  'End With

  'Make a copy of your default formula into a work string

  Dim MyFormula As String

  MyFormula = MyDefFormula

  'Replace the placeholders with the cell references

  MyFormula = Replace(MyFormula, "#A#", A.Address)

  'Note:

  '  The first arguments of Range.Address controls where the $ appears in the formula

  MyFormula = Replace(MyFormula, "#B#", B.Address(0, 0))

  'Note:

  '  The part 'External:=True' is necessary if you refer to other sheets or files

  '  But it works also if the reference is in the active sheet

  MyFormula = Replace(MyFormula, "#C#", C.Address(External:=True))

  'Write the formula into the cell

  Range("A2").Formula = MyFormula

  'Note:

  '  If you locale language is not English you can use the FormulaLocal property instead

  '  But in this case the code works only on PCs with your locale settings

  'Range("A2").FormulaLocal = MyFormula

  'Note:

  '  If you want to create array formulas use the FormulaArray property

  '  But in this case use must use English formulas, a FormulaArrayLocale doesn't exists

  'Range("A2:A10").FormulaArray = MyFormula

End Sub