Trying to run FormulaR1C1 on a dynamic named ranged

Viewed 26

Im new to VBA so please bear with me. My aim is to use the concatenate formula on a certain column in a named ranged (but only if the cell doesnt contain a specified string).

I want to do this ideally without looping.

Sub Xbuch()

Dim wb1 As Excel.Workbook
Dim wb2 As Excel.Workbook
'Dim startcell As Range
'Dim lastrow As Long


    Set wb1 = Workbooks.Open(Filename:=fullpath)
    Set wb2 = Application.ThisWorkbook
    'Set startcell = wb2.Sheets("prefixer").Range("D2")
    'Set lastrow = wb2.Cells(wb2.Rows.Count, startcell.Column).End(xlUp).Row
        
        wb1.Sheets("download").Range("C2", Range("C5").End(xlDown)).Copy _
            wb2.Worksheets("Prefixer").Range("C2")

    wb2.Worksheets("Prefixer").Range("FinalList").Cells(1,1).FormulaR1C1 =
        "=IF(RC[-1]<>""XON"",CONCATENATE(R2C[-3],RC[-1]),RC[-1])"

Getting a syntax error here.

Would appreciate any help :)

0 Answers
Related