Solved: Cell Value Question - Error Handling?

[NateW]

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

[NateW]

Note, in one section I used iserror to catch the errors, with success. The problem is where I have to capture data from three or four columns from the source workbook, and put it into the target workbook in one column - I think using iserror would equate to waaaaay too much error handling code...

[mikerickson]

Its not the moving, it's the testing that is causing the problem.

One way would be instead of looping with varMasterRow, use a range variable to loop through


Set BigRange = Workbooks(varWorkbook).Sheets(sheet).Cells(6,2)
Set BigRange = Range(BigRange,BigRange.End(xlDown))

For each oneCell in BigRange.SpecialCells(xlCellTypeConstants)
varMasterRow = oneCell.Row
Rem comment out old Do
Rem the rest of the code
Rem comment out old Loop
Next oneCell or xlCellTypeFormulas

[NateW]

Hmm...I'm not really sure what to do with that. All I'm looking for, if possible, is an alternative to .value that will allow the error message #n/a to transfer across, instead of giving me an error. Is this even possible?

[Norie]

Can you actually tell us where the error occurs?

Have you tried Text instead of Value?

[NateW]

Yes...same error occurs.

[NateW]

Error occurs at


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 & " " & _

[mikerickson]

The type mismatch error is not caused by the line

Range1.Value = Range2.Value Changing to a different property will not prevent the error.

It is caused by the line preceeding the highlited line. I'm guessing that would be

If Range1.Value = "" Then

The error is caused because the operator "=" can not handle the data type Error values that are in #NA cells. (This is similar to "="'s inability to handle range variables.)

Edit: Further investigation has shown I was in error. "=" can compare Error values. But only with other Error values; using "=" to compare an Error with any other data type will cause the Type Mismatch error.