VBA Error 91: Object variable or With block variable not set

[Good Day]

Hi I have been trying to search for a solution for days to no avail. Basically below code should take a specific value (C2) from ThisWorkbook and find if it exists in Sample Db (Column D), if it doesn't exist, it will populate a range of data (Column A to AI) from ThisWorkbook to Sample Db, if it exist, it will update existing values in the same range in Sample Db. However I'm hitting error 91 at the dStartRow line and can't execute the main function of transferring data to Sample Db, could anyone help please?

Sub Track()
Dim wb As Workbook, wsCopy As Worksheet, wsDest As Worksheet, i As Integer, j As Integer, cStartRow As Long, cEndRow As Long, dStartRow As Long, dEndRow As Long, rFndCell As Range, stFnd As String
Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:\Users\xyz123\Desktop\Sample Tracker\Sample Db.xlsx")
Set wsCopy = ThisWorkbook.Worksheets("Summary")
Set wsDest = Workbooks("Sample Db.xlsx").Worksheets("Sample Db")
stFnd = wsCopy.Range("C2").Value
j = wsDest.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
cStartRow = wsCopy.Range("C:C").Find(what:=stFnd, after:=wsCopy.Range("C1"), LookIn:=xlValues).Row
cEndRow = wsCopy.Range("C:C").Find(what:=stFnd, after:=wsCopy.Range("C1"), LookIn:=xlValues, searchdirection:=xlPrevious).Row
dStartRow = wsDest.Range("D:D").Find(what:=stFnd, after:=wsDest.Range("D1"), LookIn:=xlValues).Row
dEndRow = wsDest.Range("D:D").Find(what:=stFnd, after:=wsDest.Range("D1"), LookIn:=xlValues, searchdirection:=xlPrevious).Row
    With wsDest
    Set rFndCell = .Range("D:D").Find(stFnd, LookIn:=xlValues)
        For i = 2 To 5
            If rFndCell Is Nothing And wsCopy.Cells(i, "C").Value <> "" Then
            With wsDest
                .Range("B" & j & ":AJ" & j).Value = wsCopy.Range("A" & i & ":AI" & i).Value
                j = j + 1
            End With
            Else
            With wsDest
                .Range("V" & dStartRow & ":V" & dEndRow).Value = wsCopy.Range("U" & cStartRow & ":U" & cEndRow).Value
            End With
            End If
        Next
    wb.Save
    wb.Close
    End With
    If rFndCell Is Nothing Then
        MsgBox "Request has been submitted!", vbInformation, "Success"
    Else
        MsgBox "Information has been updated", vbInformation, "Success"
    End If
Application.ScreenUpdating = True
End If
End Sub

 

[Joe4]

Welcome to the Board!

Whenever you get "error 91" on a line of code that is using "Find", it usually means that it cannot find the value that you are looking for (that what happens when you cannot find it). So you need to determine what should happen when it cannot find the value, and amend your code to address that situation (i.e. adding error handling to address the situation).

If you do not know how to amend your code to do that, please tell us what you want to happen when you run across this situation (when it does not find the value you are looking for).

 

[Good Day]

Hi Joe4, thank you very much for the prompt response. I came across this suggestion and have tried remedying my code but still couldn't get it to work, in my case, I would like to find the value in Column D in Sample Db Workbook, if couldn't be found, code will take a range of data from A:AI in ThisWorkbook and transfer to B:AJ in Sample Db. If value is already in database, code will search for the 'position' of the range of data in Sample Db using first and last row method and update the existing values. Notice I'm only updating Column V details because I'm testing out the code so far (not successful), I will add in more columns once the code is correct, I hope!

 

[Joe4]

Actually, I see another issue. You have two "If" statements, but three "End If" statements.

I think you need to remove the last "End If" before the "End Sub" line.

 

[dmt32]

Hi,
untested & bit of a guess but see if this update to your code helps you

Sub Track()
    Dim wb          As Workbook
    Dim wsCopy      As Worksheet, wsDest As Worksheet
    Dim i           As Long, j As Long
    Dim dStartRow   As Range, dEndRow As Range
    Dim rFndCell    As Range, cStartRow As Range, cEndRow As Range
    Dim stFnd       As String
    
    Application.ScreenUpdating = False
    
    On Error GoTo myerror
    Set wsCopy = ThisWorkbook.Worksheets("Summary")
    stFnd = wsCopy.Range("C2").Value
    
    Set wb = Workbooks.Open("C:\Users\xyz123\Desktop\Sample Tracker\Sample Db.xlsx")
    
    Set wsDest = wb.Worksheets("Sample Db")
    j = wsDest.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Row
    
    Set cStartRow = wsCopy.Range("C:C").Find(what:=stFnd, after:=wsCopy.Range("C1"), LookIn:=xlValues)
    
    If cStartRow Is Nothing Then
        Err.Raise 53, , , stFnd & " In Column C Not Found"
    Else
        Set cEndRow = wsCopy.Range("C:C").Find(what:=stFnd, after:=cStartRow, LookIn:=xlValues, searchdirection:=xlPrevious)
        If cEndRow Is Nothing Then Set cEndRow = cStartRow
    End If
    
    Set dStartRow = wsDest.Range("D:D").Find(what:=stFnd, after:=wsDest.Range("D1"), LookIn:=xlValues)
    
    If dStartRow Is Nothing Then
        Err.Raise 53, , stFnd & " In Column D Not Found"
    Else
        Set dEndRow = wsDest.Range("D:D").Find(what:=stFnd, after:=dStartRow, LookIn:=xlValues, searchdirection:=xlPrevious)
        If dEndRow Is Nothing Then Set dEndRow = dStartRow
    End If
    
    With wsDest
        Set rFndCell = .Range("D:D").Find(stFnd, LookIn:=xlValues)
        For i = 2 To 5
            If rFndCell Is Nothing And wsCopy.Cells(i, "C").Value <> "" Then
                .Range("B" & j & ":AJ" & j).Value = wsCopy.Range("A" & i & ":AI" & i).Value
                j = j + 1
            Else
                .Range("V" & dStartRow.Row & ":V" & dEndRow.Row).Value = wsCopy.Range("U" & cStartRow.Row & ":U" & cEndRow.Row).Value
            End If
        Next
    End With
    
    wb.Close True
    Set wb = Nothing
    
    MsgBox IIf(rFndCell Is Nothing, "Request has been submitted!", _
                                    "Information has been updated"), vbInformation, "Success"

    
myerror:
    If Not wb Is Nothing Then wb.Close False
    Application.ScreenUpdating = True
    If Error <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Hope Helpful

Dave