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

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