Run time error 1004 - vba formula with variables

Question

I'm trying to get some code together which pastes in a match function in column "B", which references the same row in adjacent column "A", using a list in column "A" in another sheet as the lookup array. The worksheet this all happens in is ws0, and the sheet the lookup array is in is ws2.

The code trips up on the last line (SKIP TO THE BOTTOM OF MY CODE) in the "With" brackets:

Cells(i, 2).Value = "=MATCH(A" & i & "," & ws2.Name & "!$A$9:$A$" & lRow1 & ", 0)""",

giving me a

Run-time error 1004. Application-defined or object-defined error.

I can't figure out what is causing this. I have a feeling my quotes may be incorrect, or one of my variables lRow1, fRow, ws2 may be a problem, but I've tinkered with them all as much as I can, without avail. Any thoughts would be appreciated! Note, the line commented out above the one throwing the error demonstrates what the formula's result ought to look like.

Dim wb0 As Workbook 'this workbook (the log) '''''''''''''''''''''''
Dim wb1 As Workbook 'the opened T12 sheet
Dim rng0 As Range 'the range of cells in this workbook's 2nd sheet to be copied to
Dim rng1 As Range 'the range of cells from the opened workbook to be copied from
Dim rng2 As Range 'the range of the cells copied into ws0 from ws1
Dim fRow As Integer 'the first row of the range used in wb0, ws0
Dim lRow0 As Integer 'the last row of the range used in wb0, ws0
Dim lRow1 As Integer 'the last row of the range of accounts in ws2
Dim usedRng As Range 'the full range of cells used in wb0, ws0

Set ws0 = ActiveSheet
Set wb0 = ActiveWorkbook '''''''''''''''
Set rng0 = ws0.Range("A9:A159")

'Find the desired T12 workbook filepath
  'only allow the user to select one file
  Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
  'make the file dialog visible to the user
  fileChoice = Application.FileDialog(msoFileDialogOpen).Show
  'determine what choice the user made
  If fileChoice <> 0 Then
      'get the file path selected by the user
      filePath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
  End If

'Set variables using the newly-opened workbook
Set wb1 = Workbooks.Open(filePath)
Set ws1 = ActiveSheet
Set rng1 = ws1.Range("B9:B159")

'copy wb1, ws1's into wb0, ws0
rng0.Value = rng1.Value
wb1.Close

'create variable to refer to the first tab of wb0
wb0.Worksheets("Sheet1").Activate
Set ws2 = ActiveSheet

With ws2
    lRow1 = Cells(.Rows.Count, "A").End(xlUp).Row 'Last row
End With

'switch back to ws0
ws0.Activate

'identify the range used in ws0 of wb0
Set rng2 = ActiveSheet.UsedRange

fRow = rng2(1).Row 'First row

With ws0
    lRow0 = Cells(.Rows.Count, "A").End(xlUp).Row 'Last row
End With

Dim i As Integer
For i = fRow To lRow0
    With ws0
        'Cells(i, 2).Value = "=MATCH(A9,Sheet2!A9:A100,0)"
        Cells(i, 2).Value = "=MATCH(A" & i & "," & ws2.Name & "!$A$9:$A$" & lRow1 & ", 0)"""
    End With
Next i

End Sub

I get the same error with Cells.Formula..., and with Range.Formula... I get a different run time error 1004: "Method 'Range' of object _ Global failed." Any suggestions would be appreciated! I included the full chunk of my macro for your reference...although the relevant part is at the bottom. Thanks in advance!

Answer 1

I think it's your )""" you only need 1 closing "