excel - Two sheets in the same workbook debugging error?

Question

CLOSED I HAVE A WORKING SOLUTION FOR THIS.

I am attempting to use a name of a sheet inside of a workbook. My two sheets are called "Bank"(Sheet1) & "Replicon"(Sheet2). When I run my code that seems functional I get a run time error with ThisWorkbook.Worksheets("Bank") any variation of this line of code.

Ultimately I just want Column J in Sheet 1 to populate with matching Project Names from Column E in sheet 2.

What am I missing here? I will post my full code as it might be of some help.

    Sub ProjectName()

Dim UserID As String, Day As String, Money As String
Dim r As Integer, s As Integer
Dim lr As Long, lr2 As Long
With ActiveSheet
Dim wsBank As Worksheet, wsRep As Worksheet
Set wsBank = Worksheets("Bank")
Set wsRep = Worksheets("Replicon")

    lr = wsBank.Cells(wsBank.Rows.Count, 1).End(xlUp).Row
    lr2 = wsRep.Cells(wsRep.Rows.Count, 1).End(xlUp).Row

For r = 2 To lr
  UserID = wsBank.Cells(r, 1).Value
  Day = wsBank.Cells(r, 5).Value
  Money = wsBank.Cells(r, 6).Value
  For s = 2 To lr2
    If wsRep.Cells(s, 1).Value = UserID And wsRep.Cells(s, 2).Value = Day And wsRep.Cells(s, 3).Value = Money Then
      wsBank.Cells(s, 10).Value = wsRep.Cells(s, 5).Value
    End If
    Next s
  Next r

End with End Sub

Sheet1(Bank) Sheet2(Replicon)

question from:https://stackoverflow.com/questions/65848918/two-sheets-in-the-same-workbook-debugging-error

Answer 1

Your if statement is broken since VBA stops at the line-end, you may try:

If ThisWorkbook.Worksheets("Replicon").Cells(s, 1).Value = UserID And _
   ThisWorkbook.Worksheets("Replicon").Cells(s, 2).Value = Day And _
   ThisWorkbook.Worksheets("Replicon").Cells(s, 3).Value = Money Then

   ThisWorkbook.Worksheets("Bank").Cells(s, 10).Value = _
   ThisWorkbook.Worksheets("Replicon").Cells(s, 5).Value
End If

With the "_" you tell VBA that your statment will continue in the next line.