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

Good Day

New Member
Joined
Oct 19, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,836
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 19, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,836
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,170
Office Version
  1. 2019
Platform
  1. Windows
Hi,
untested & bit of a guess but see if this update to your code helps you

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,863
Messages
5,542,941
Members
410,577
Latest member
ZvK
Top