Expert: Aidan Heritage - 4/1/2008

Question
Hey,
I am trying to make a Macro, which when activated, will fill a certain column with a formula and then do a Paste Values for the formula. Here is what I have written. The Macro gives an error at the stage "ActiveCell.FormulaR1C1". I am assuming that the error it is facing at the first "ActiveCell.FormulaR1C1", it will face the same at the others too. Please Help.

Sub Word_Summary()
'
' Word_Summary Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
   Range("CP2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.NumberFormat = "General"
   Range("CP2").Select
   ActiveCell.FormulaR1C1 = "=IF(CA2="",""Error"",IF(CI2<>"",CONCATENATE(CA2,"", "",CC2,"", "",CE2,"", "",CG2,"" and "",CI2),IF(CG2<>"""",CONCATENATE(CA2,"", "",CC2,"", "",CE2,"" and "",CG2),IF(CE2<>"""",CONCATENATE(CA2,"", "",CC2,"" and "",CE2),IF(CC2<>"""",CONCATENATE(CA2,"" and "",CC2),CA2)))))"
   Range("CP2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.FillDown
   Range("CP2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
   
   Range("BS2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.NumberFormat = "General"

   Range("BZ2").Select
   
   If ActiveCell.FormulaR1C1 = "MR" Or ActiveCell.FormulaR1C1 = "PEP-MR" Or ActiveCell.FormulaR1C1 = "Risk" Or ActiveCell.FormulaR1C1 = "PEP-Risk" Or ActiveCell.FormulaR1C1 = "AC" Or ActiveCell.FormulaR1C1 = "PEP-AC" Then
   
   Range("BS2").Select

   ActiveCell.FormulaR1C1 = "=IF(AND(F2<>"""",OR(C2<>"""",D2<>"""",E2<>"""")),""Error"",IF(OR(BG2="""",AND(C2="""",D2="""",E2="""",F2="""")),""Error"",CONCATENATE(IF(AND(C2="""",D2="""",E2=""""),F2,IF(D2="""",CONCATENATE(C2,"" "",E2),CONCATENATE(C2,"" "",D2,"" "",E2))),IF(OR(BZ2=""MR"",BZ2=""PEP-MR""),CONCATENATE("" Text..... "",CP2,"" Text..... "",BG2,"".""),IF(OR(BZ2=""Risk"",BZ2=""PEP-Risk""),CONCATENATE("" Text..... "",CP2,"" Text..... "",BG2,"".""),IF(OR(BZ2=""AC"",BZ2=""PEP-AC""),CONCATENATE("" Text..... "",CP2,"" Text..... "",BG2,"".""),""Error""))))))"
   
   Range("BS2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.FillDown
   Range("BS2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
   Range("BS1").Select
   
   Else
   
   Range("BS2").Select
   
   ActiveCell.FormulaR1C1 = "=IF(AND(F2<>"""",OR(C2<>"""",D2<>"""",E2<>"""")),""Error"",IF(OR(BG2="""",AND(C2="""",D2="""",E2="""",F2="""")),""Error"",CONCATENATE(IF(AND(C2="""",D2="""",E2=""""),F2,IF(D2="""",CONCATENATE(C2,"" "",E2),CONCATENATE(C2,"" "",D2,"" "",E2))),IF(OR(BZ2=""MRD"",BZ2=""PEP-MRD""),CONCATENATE("" Text..... "",CP2,"" Text..... "",BG2,"".""),IF(OR(BZ2=""High Risk"",BZ2=""PEP-High Risk""),CONCATENATE("" Text..... "",CP2,"" Text..... "",BG2,"".""),IF(OR(BZ2=""AFR"",BZ2=""PEP-AFR""),CONCATENATE("" Text..... "",CP2,"" Text..... "",BG2,"".""),""Error""))))))"
   
   Range("BS2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.FillDown
   Range("BS2").Select
   Range(Selection, Selection.End(xlDown)).Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False
   Range("BS1").Select
   
   End If
   
End Sub

Answer
You are using R1C1 BUT the formula DOESN'T use this method - amend it to activecell.formula.  Having said that, I wouldn't put a formula in place if it is to be replaced with values, rather use the code to work out what the value should be and set it directly