英文:
VBA run-time error '91' for excel worksheet imports
问题
以下是您提供的 VBA 代码的中文翻译：
选项 显式
Sub IMPORT()
'声明变量
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
'清除单元格内容
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
'打开 ASI 主排程工作簿
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")
'设置变量
For i = 2 To 40
job = Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "A") '作业值保存为第 i 行，列 A
Set rng = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Range("R:R").Find(job) '在主排程中查找作业值
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) '在 DAILYTONNAGELOG 中查找作业值
tosales = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CH") '从主排程中获取 TO SALES 值
PM = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "BH") '从主排程中获取 PM 值
loaddate = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "L") '从主排程中获取 LOAD DATE 值
shopdate = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "DD") '从主排程中获取 SHOP DATE 值
detailer = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "DB") '从主排程中获取 DETAILER 值
rapp = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CW") '从主排程中获取 RETURNED APPROVALS DATE 值
engrdue = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CJ") '从主排程中获取 ENGINEERING DUE DATE 值
engrout = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CK") '从主排程中获取 ENGINEERING RELEASE DATE 值
engr = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CL") '从主排程中获取 ENGINEER 值
dftdue = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CN") '从主排程中获取 DRAFTING DUE DATE 值
dftout = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CO") '从主排程中获取 DRAFTING RELEASE DATE 值
drafter = Workbooks("ASI Master Schedule.xlsx").Worksheets("Master Schedule").Cells(rng.Row, "CP") '从主排程中获取 DRAFTER 值
AB = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "X") '从 CCCCC 中获取 AB 日期值
atype = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "AC") '从 CCCCCC99 中获取 RETURNED APPROVALS TYPE 值
edwgs = Workbooks("CCCCC99.xls").Worksheets("JOB INFO").Cells(rng2.Row, "Y") '从 CCCCCC99 中获取 ACTUAL DATE APPROVALS EMAILED 值
'将值放入目标工作表的相应单元格
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "B") = tosales '将 TO SALES 放入第 i 行，列 B
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "Q") = PM '将 PM 放入第 i 行，列 Q
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "P") = loaddate '将 LOAD DATE 放入第 i 行，列 P
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "O") = shopdate '将 SHOP DATE 放入第 i 行，列 O
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "N") = detailer '将 DETAILER 初始值放入第 i 行，列 N
Workbooks("PROJECT MANAGEMENT MATRIX A.xlsm").Worksheets("MASTER SCHEDULE").Cells(i, "L") =
<details>
<summary>英文:</summary>
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.
```lang-vb
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
答案1
得分: 3
以下是代码的翻译部分：
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")
'清除之前的数据
For Each addr in Array("B2:F2","H2:P2")
wsMaster.Range(addr).Resize(39).ClearContents
Next addr
'打开工作簿并分配工作表变量
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")
'传输数据以及查找
For i = 2 To 40
job = wsMaster.Cells(i, "A").Value
'获取与此工作匹配的行（或如果没有匹配则返回错误值）
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
'...
'等等
'...
.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
'如果`rw`不是错误值，则返回工作表`ws`、行`rw`和列`col`中的值；否则返回"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
希望这有所帮助！
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论