If temp is returning an error (#n/a, #div/0, etc), then using that in the
comparison will cause an error:
* If temp = 0 Or temp = "" Then FileDone = True *
I'd check first:
if iserror(temp) then
'do what you want
else
if temp= 0 _
or temp = "" then
Filedone = true
end if
end if
(I like the multi-line if/end if better--personal choice!)
paulharvey wrote:
I have a GetValue function which returns a cell's value from a closed
workbook using the ExecuteExcel4Macro command. I have no problem
assigning the returned value to the corresponding cell of the active
sheet. However, when I store the return value in a variant called
"temp" to check for empty cells, half of the time it runs smoothly and
the other half it returns the error "Type mismatch". I bolded the line
that returns an error half of the time. Any suggestions?
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
arg = "'" & path & "[" & file & "]" & sheet & "'!" &
Range(ref).Range("A1").address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
Private Sub FilterData(InputFile, InputSheet, InputRow, OutputRow,
FileDone)
Dim temp As Variant
p = PathName(InputFile)
f = FileName(InputFile)
s = InputSheet
Application.ScreenUpdating = False
Do
temp = GetValue(p, f, s, Cells(InputRow, 1).address)
* If temp = 0 Or temp = "" Then FileDone = True *
If Not FileDone Then
For c = 1 To 20
a = Cells(InputRow, c).address
ActiveSheet.Cells(OutputRow, c) = GetValue(p, f, s, a)
Next c
InputRow = InputRow + 1
OutputRow = OutputRow + 1
End If
Application.ScreenUpdating = True
Exit Do
FileDone = True
Loop
End Sub
--
paulharvey
------------------------------------------------------------------------
paulharvey's Profile: http://www.excelforum.com/member.php...o&userid=25227
View this thread: http://www.excelforum.com/showthread...hreadid=387547
--
Dave Peterson