Invalid watch expression when Inserting formula using For Next Loop because of bad Syntax-CodePudding

I am new to VBA in excel all though I have been working with it in Access for about 6 months now. I know that my syntax is what is causing this error message, I just don't know where I went wrong in my formula. I am attempting to concatenate a columns worth of fields (~900 at the moment) from 2 other fields in the same sheet.

I am trying to create a macro to enter the formula into Column C, but honestly inserting strings is my downfall, I just can't keep all of the quotation marks straight. Below is the code I have written so far - please tell me how much I've messed up!

Sub Concatenate()
    
    Dim i As Long
    Dim LastRow As Long
    Dim Con As String
    Dim WS As Worksheet
    Set WS = Sheets("Vlookups")
    
    
    'Set upper range of Loop
    LastRow = Range("C" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    'Set to Active Worksheet
    Worksheets("Vlookups").Activate
    
    'Explicitly reference the Sheet when calling for any Range or Cell
    With WS

    For i = 2 To LastRow
    
    Con = "=CONCATENATE(" & .Cells(i, 15).Select & "," & "-" & "," & .Cells(i, 16).Select & ")"
    
        .Cells(i, 3).Select
        ActiveCell.Formula = Con
        
    Next i
    End With
    
    Application.ScreenUpdating = False

End Sub

================================================================ Leaving the original question and code up for anyone searching in the future. Below is the code that ended up solving my problem, provided by @BigBen.

Sub Concatenate()
    
    Dim LastRow As Long
    
    'Set upper range of Loop
    LastRow = Range("O" & Rows.Count).End(xlUp).Row
    
    'Reduce time taken to run Macro
    Application.ScreenUpdating = False
    
    WS.Range("C2:C" & LastRow).Formula = "=O2&""-""&P2"
    
    Application.ScreenUpdating = True
    
End Sub

CodePudding user response：

There is no need to Select or Activate.
There is no need to loop.
It looks like you are finding the last row based on column C, but then writing the formula into column C, which seems suspect. Perhaps find the last row based on column O, or column P?
There's no need to use the CONCATENATE formula.

With WS
    ' find last row based on column O, or maybe P
    LastRow = .Range("O" & .Rows.Count).End(xlUp).Row
    .Range("C2:C" & LastRow).Formula = "=O2&""-""&P2"
End With

If you actually want hard-coded strings instead of cell references in your formula, then:

With WS
    ' find last row based on column O, or maybe P
    LastRow = .Range("O" & .Rows.Count).End(xlUp).Row

    For i = 2 to LastRow
       Range("C" & i).Formula = "=""" & Range("O" & i).Value & "-" & Range("P" & i).Value & """"
    Next 
End With