VBA run-time error '91' for excel worksheet imports

Question

I am attempting to pull cell data from a few different referenced excel workbooks. I am fairly new to VBA so my code is probably unnecessarily long, but everything works just fine until one of the referenced cells do not exist. At that point, it gives me the 91 error. What I would like to accomplish is this. When a value from the worksheet that I reference does not exist, I would like the program to skip that reference and continue on to the next row. When I use error handling, it actually copies the row above into the row with the missing data which is less than optimal. How do I accomplish this to have it just skip and leave the cells in the destination worksheet blank, or even better, have it put "No data"?

This is the code that I currently use.

Option Explicit
Sub IMPORT()

'DECLARE VARIABLES
Dim job As String
Dim engrdue, dftdue As String
Dim engrout, dftout As String
Dim engr, drafter As String
Dim tosales, PM As String
Dim loaddate, shopdate As String
Dim detailer As String
Dim rapp As String
Dim atype As String
Dim edwgs As String
Dim earlyab As String
Dim i As Integer
Dim AB As String
Dim abreq As String
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim result As Range



'CLEAR CELLS
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("B2", "F40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("H2", "I40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("J2", "J40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("P2", "P40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("O2", "O40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("N2", "N40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("L2", "L40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("M2", "M40").ClearContents
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Range("K2", "K40").ClearContents


'OPEN THE ASI MASTER SCHEDULE WORKBOOK
Workbooks.Open ("S:\Scheduling\ASI Master Schedule\ASI Master Schedule.xlsx")

Workbooks.Open ("S:\Drafting_Detailing\CCCCC_\CCCCC99.xls")

Workbooks.Open ("S:\Scheduling\ASI Master Schedule\DAILYTONNAGELOG.xls")



'SET VARIABLES
For i = 2 To 40
    job = Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "A")                 'JOB VALUE SAVED AS ROW I, COLUMN A
    Set rng = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Range("R:R").Find(job)            'FIND JOB VALUE IN MASTER SCHEDULE
    Set rng2 = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Range("A:A").Find(job)
    Set rng3 = Workbooks("DAILYTONNAGELOG.xls").Worksheets("Archive").Range("A:A").Find(job)                       'FIND JOB VALUE IN DAILYTONNAGELOG
       
'        If IsEmpty(job) Then
'        If rng Is Nothing Then
'        If rng Is Not Nothing Then
'        If [cell] is nothing then
'        If [cell] Is Not Nothing Then
'        If Not IsError(job) Then
'        If Not IsError(rng) Then
'        If Not IsError(rng2) Then
'        If Not IsError(rng3) Then
'        If Not IsError("ASI Master Schedule.xlsx") Then
'        If Not IsError(rng.Row, "CH") Then
'            GoTo TheEnd
        
'       Else
            tosales = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CH")      'GRAB TO SALES FROM MASTER SCHEDULE
            PM = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "BH")           'GRAB TO SALES FROM MASTER SCHEDULE
            loaddate = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "L")      'GRAB LOAD DATE FROM MASTER SCHEDULE
            shopdate = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "DD")     'GRAB SHOPPED DATE FROM MASTER SCHEDULE
            detailer = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "DB")     'GRAB DETAILER NAME FROM MASTER SCHEDULE
            rapp = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CW")         'GRAB RETURNED APPROVALS DATE FROM MASTER SCHEDULE
            engrdue = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CJ")      'GRAB ENGINEERING DUE DATE FROM MASTER SCHEDULE
            engrout = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CK")      'GRAB ENGINEERING RELEASE DATE FROM MASTER SCHEDULE
            engr = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CL")         'GRAB ENGINEER FROM MASTER SCHEDULE
            dftdue = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CN")       'GRAB DRAFTING DUE DATE FROM MASTER SCHEDULE
            dftout = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CO")       'GRAB DRAFTING RELEASE DATE FROM MASTER SCHEDULE
            drafter = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CP")      'GRAB DRAFTER FROM MASTER SCHEDULE
            AB = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "X")                               'GRAB AB DATE FROM CCCCC
            atype = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "AC")                           'GRAB RETURNED APPROVALS TYPE FROM CCCCCC99
            edwgs = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "Y")                            'GRAB ACTUAL DATE APPROVALS EMAILED FROM CCCCCC99

            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "B") = tosales     'PUT NTP DATE IN ROW i, COLUMN B
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "Q") = PM          'PUT PM IN ROW i, COLUMN Q
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "P") = loaddate    'PUT LOAD DATE IN ROW i, COLUMN P
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "O") = shopdate    'PUT SHOP DATE IN ROW i, COLUMN O
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "N") = detailer    'PUT DETAILER INITIALS IN ROW i, COLUMN N
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "L") = rapp        'PUT RETURNED APPROVALS DATE ROW i, COLUMN L
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "M") = atype       'PUT RETURNED APPROVALS TYPE ROW i, COLUMN M
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "C") = engrdue     'PUT ENGR DUE DATE IN ROW i, COLUMN C
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "D") = engrout     'PUT ENGR RELEASE DATE IN ROW i, COLUMN D
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "E") = engr        'PUT ENGINEER IN ROW i, COLUMN E
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "F") = dftdue      'PUT DFT DUE DATE IN ROW i, COLUMN F
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "H") = AB          'PUT AB RELEASE DATE IN ROW i, COLUMN H
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "I") = dftout      'PUT DFT RELEASE DATE IN ROW i, COLUMN I
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "J") = drafter     'PUT DRAFTER IN ROW i, COLUMN J
            Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "K") = edwgs       'PUT ACTUAL DATE APPROVALS EMAILED IN ROW i, COLUMN K

'End If

Next i

'TheEnd:

End Sub

Tim Williams · Answer 1 · 2023-07-14T18:39:45.510

A few suggestion below for how you can improve things:

Option Explicit


Sub IMPORT()

    Dim wsMaster As Worksheet, addr
    Dim wbASI As Workbook, wb99 As Workbook, wbTonnage As Workbook
    Dim wsASI As Worksheet, ws99 As Worksheet, wsTonnage As Worksheet
    Dim i As Long
    Dim job As String, c As Range, addr
    Dim jobRow1, jobRow2, jobRow3
    
    Set wsMaster = ThisWorkbook.Worksheets("MASTER SCHEDULE")
    
    'clear previous
    For Each addr in Array("B2:F2","H2:P2")
        wsMaster.Range(addr).Resize(39).ClearContents
    Next addr
    
    'open workbooks and assign worksheet variables
    Set wbASI = Workbooks.Open("S:\Scheduling\ASI Master Schedule\ASI Master Schedule.xlsx")
    Set wsASI = wbASI.Worksheets("Master Schedule")
    
    Set wb99 = Workbooks.Open("S:\Drafting_Detailing\CCCCC_\CCCCC99.xls")
    Set ws99 = wb99.Worksheets("JOB INFO")
    
    Set wbTonnage = Workbooks.Open("S:\Scheduling\ASI Master Schedule\DAILYTONNAGELOG.xls")
    Set wsTonnage = wbTonnage.Worksheets("Archive")

    'transfer data along with lookups
    For i = 2 To 40
        job = wsMaster.Cells(i, "A").Value
        
        'get matching row for this job (or error value if no match)
        jobRow1 = Application.Match(job, wsASI.Range("R:R"), 0)
        jobRow2 = Application.Match(job, ws99.Range("A:A"), 0)
        jobRow3 = Application.Match(job, wsTonnage.Range("A:A"), 0)
        
        With wsMaster.Rows(i)
            .Columns("B").Value = Fetch(wsASI, jobRow1, "CH")   'NTP DATE
            '...
            'etc
            '...
            .Columns("F").Value = Fetch(wsASI, jobRow1, "CP")   'DFT DUE DATE
            .Columns("H").Value = Fetch(ws99, jobRow2, "X")     'AB RELEASE DATE
            '....
            .Columns("K").Value = Fetch(ws99, jobRow2, "Y")     'ACTUAL DATE APPROVALS EMAILED
        End With
    Next i
End Sub

'If `rw` is not an error value, return value from worksheet `ws`,
' row `rw` and column `col`; else return "No data"
Function Fetch(ws As Worksheet, rw As Variant, col As String)
    If Not IsError(rw) Then
        Fetch = ws.Cells(rw, col).Value
    Else
        Fetch = "No data"
    End If
End Function

For OP: `If c.Address = "$B$2" Then c.Resize(39, 5).ClearContents Else c.Resize(39).ClearContents End If` since you wanted to clear B to F (even though the clearing of content isn't quite needed if you're overwriting everything anyway..) — Notus_Panda, Jul 14 '23 at 09:50
@Notus_Panda - Thanks I missed that not all the ranges were single-column. Edited to consolidate the columns, and change the approach. — Tim Williams, Jul 14 '23 at 16:05
Sorry to butt in again, `Next c` should probably be `Next addr` — Notus_Panda, Jul 14 '23 at 18:30
If this answered your question, please flag as "Accepted", to help anyone coming along later with a similar problem. — Tim Williams, Jul 18 '23 at 21:48

VBA run-time error '91' for excel worksheet imports

1 Answers1