NateW
11-30-2007, 08:09 AM
Hi, Folks.
I am trying to bring across cell values from one workbook to another, and for the most part it works, however sometimes the source workbook contains errors, causing my macro to breakdown.
Essentially, any time the macro finds "#n/a", it stops the macro with a type mismatch error.
My question is, instead of using cell.value, is there a way I can bring across just whatever is in the cell - including #n/a? If I were to do it manually, I would paste special, then choose value. Using .value here gets me the error.
Any suggestions?? Thanks so much in advance!!
Here is the sub that I'm using:
Sub Imports()
Dim varMasterRow, VarRow, sheet As Integer
Dim varWorkbook, DateString As String
Dim varDate As Date
varWorkbook = ActiveWorkbook.Name
Application.ScreenUpdating = False
ThisWorkbook.Activate
varMasterRow = 4
VarRow = 6
Do Until Sheets("Gate Control").Cells(varMasterRow, 6).Value = ""
varMasterRow = varMasterRow + 1
Loop
For sheet = 1 To 7
DateString = Right(Trim(Workbooks(varWorkbook).Sheets(sheet).Cells(3, 9).Value), 8)
varDate = DateSerial(Right(DateString, 2), Mid(DateString, 4, 2), Left(DateString, 2))
VarRow = 6
Do Until Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 2).Value = ""
If Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 9).Value <> "" Then
'Sheets("Gate Control").Cells(varMasterRow, 1).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(varRow, 9).Value
Sheets("Gate Control").Cells(varMasterRow, 3).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 8).Value _
& ": " & Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 9).Value & _
" " & Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 7).Value & " " & _
Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 10).Value
Sheets("Gate control").Cells(varMasterRow, 2).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 4).Value
If IsError(Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 6).Value) Then
Sheets("gate control").Cells(varMasterRow, 4).Value = "REC N/A"
Else
Sheets("gate control").Cells(varMasterRow, 4).Value = _
"REC" & " " & Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 6).Value
End If
Sheets("Gate Control").Cells(varMasterRow, 5).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 5).Value
Sheets("Gate Control").Cells(varMasterRow, 6).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 3).Value + varDate
Sheets("Gate Control").Cells(varMasterRow, 9).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 11).Value
Sheets("Gate Control").Cells(varMasterRow, 10).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 12).Value
Sheets("Gate Control").Cells(varMasterRow, 16).Value = _
Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 13).Value & " " & _
Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 14).Value
Sheets("Gate Control").Cells(varMasterRow, 17).Value = varDate
Sheets("Gate Control").Cells(varMasterRow, 17).NumberFormat = "m/d/yy" ' populates Shipment Date
Sheets("Gate Control").Cells(varMasterRow, 18).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 17).Value
varMasterRow = varMasterRow + 1
End If
VarRow = VarRow + 1
Loop
Next
Call Sort
Call Flags
Sheets("Main").Activate
End Sub
I am trying to bring across cell values from one workbook to another, and for the most part it works, however sometimes the source workbook contains errors, causing my macro to breakdown.
Essentially, any time the macro finds "#n/a", it stops the macro with a type mismatch error.
My question is, instead of using cell.value, is there a way I can bring across just whatever is in the cell - including #n/a? If I were to do it manually, I would paste special, then choose value. Using .value here gets me the error.
Any suggestions?? Thanks so much in advance!!
Here is the sub that I'm using:
Sub Imports()
Dim varMasterRow, VarRow, sheet As Integer
Dim varWorkbook, DateString As String
Dim varDate As Date
varWorkbook = ActiveWorkbook.Name
Application.ScreenUpdating = False
ThisWorkbook.Activate
varMasterRow = 4
VarRow = 6
Do Until Sheets("Gate Control").Cells(varMasterRow, 6).Value = ""
varMasterRow = varMasterRow + 1
Loop
For sheet = 1 To 7
DateString = Right(Trim(Workbooks(varWorkbook).Sheets(sheet).Cells(3, 9).Value), 8)
varDate = DateSerial(Right(DateString, 2), Mid(DateString, 4, 2), Left(DateString, 2))
VarRow = 6
Do Until Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 2).Value = ""
If Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 9).Value <> "" Then
'Sheets("Gate Control").Cells(varMasterRow, 1).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(varRow, 9).Value
Sheets("Gate Control").Cells(varMasterRow, 3).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 8).Value _
& ": " & Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 9).Value & _
" " & Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 7).Value & " " & _
Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 10).Value
Sheets("Gate control").Cells(varMasterRow, 2).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 4).Value
If IsError(Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 6).Value) Then
Sheets("gate control").Cells(varMasterRow, 4).Value = "REC N/A"
Else
Sheets("gate control").Cells(varMasterRow, 4).Value = _
"REC" & " " & Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 6).Value
End If
Sheets("Gate Control").Cells(varMasterRow, 5).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 5).Value
Sheets("Gate Control").Cells(varMasterRow, 6).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 3).Value + varDate
Sheets("Gate Control").Cells(varMasterRow, 9).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 11).Value
Sheets("Gate Control").Cells(varMasterRow, 10).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 12).Value
Sheets("Gate Control").Cells(varMasterRow, 16).Value = _
Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 13).Value & " " & _
Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 14).Value
Sheets("Gate Control").Cells(varMasterRow, 17).Value = varDate
Sheets("Gate Control").Cells(varMasterRow, 17).NumberFormat = "m/d/yy" ' populates Shipment Date
Sheets("Gate Control").Cells(varMasterRow, 18).Value = Workbooks(varWorkbook).Sheets(sheet).Cells(VarRow, 17).Value
varMasterRow = varMasterRow + 1
End If
VarRow = VarRow + 1
Loop
Next
Call Sort
Call Flags
Sheets("Main").Activate
End Sub