Object variable or With block variable not set Run-time error

Hello All,

I am having an issue with a line of code in a macro that otherwise works perfectly week in week out.

 With ThisWorkbook.Worksheets("Join_Data")

    For A = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
   Set rngX = ThisWorkbook.Worksheets(.Cells(A, 1).Value).[D:D].Find(What:=.Cells(A, 2).Value, LookAt:=xlPart).Offset(0, -2)
    rngX.Value = .Cells(A, 6).Value
    rngX.Offset(0, 4).Value = .Cells(A, 5).Value
    Next A

Please could somebody advise me as to what needs fixing in this line of code? I cant quite figure out what is different about this data set that is causing this error when it usually ruins without error.

Thank you in advance

Did you?

Dim rngX as Range

Hi John,

Thanks for the response.

Yes this is the full section of the macro.

Dim A As Long
    Dim rngX As Range

    With ThisWorkbook.Worksheets("Join_Data")

    For A = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rngX = ThisWorkbook.Worksheets(.Cells(A, 1).Value).[D:D].Find(What:=.Cells(A, 2).Value, LookAt:=xlPart).Offset(0, -2)
    rngX.Value = .Cells(A, 6).Value
    rngX.Offset(0, 4).Value = .Cells(A, 5).Value
    Next A

    End With

Try:

Sub Ethanrholt()
    Dim A As Long
    Dim rngX As Range

    With Sheets("Join_Data")

    For A = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rngX = Sheets(.Cells(1, "A").Value).[D:D].Find(What:=.Cells(2, "A").Value, LookAt:=xlPart).Offset(0, -2)
    rngX.Value = .Cells(6, "A").Value
    rngX.Offset(0, 4).Value = .Cells(5, "A").Value
    Next A

    End With
End Sub

AFAIK rows before columns when using cells as range.

Apologies... the error now reads
"Run-time error '9':
Subscript out of range"

Hmm, Thanks for the feedback John but that appeared to give the same error.

Norie i get an error on this line:

Dim A As Long
    Dim rngX As Range

    With ThisWorkbook.Worksheets("Join_Data")

    For A = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
    Set rngX = ThisWorkbook.Worksheets(.Cells(A, 1).Value).[D:D].Find(What:=.Cells(A, 2).Value, LookAt:=xlPart).Offset(0, -2)
    rngX.Value = .Cells(A, 6).Value
    rngX.Offset(0, 4).Value = .Cells(A, 5).Value
    Next A

    End With

Norie that seems to work perfectly.

Thank you. Much appreciated

Please advise what that must mean in regards to why the the initial issue came about?

The message box did appear for one line of data in the one of the data sheets. After skipping that the rest of the macro ran without error.