Weird... type mismatch only half of the time

[paulharvey]

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

[Dave Peterson]

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