Hello,

This is my first posting on this forum. I first posted my question on
the Mr Excel Message Board -
http://www.mrexcel.com/board2/viewto...891&highlight=
.... I got some help, but i'm still having some problems.


Here's what I need to do:

I have a worksheet that lists 424 records displaying "file names" (in
column A) and "line numbers" (in column C). I'm using the vlookup
funciton in my macro to retrieve explanations from files in another
folder.
'-----------------------------------------------
Sub Option1()
Dim MyCount
MyCount = Range(Range("A5"), Range("A5").End(xlDown)).count + 4
For i = 5 To MyCount
Cells(i, 15).Formula = "=VLOOKUP(C" & i & ",'C:\My
Documents\Survey\[" & Cells(i, 1) & _
".xls]Explanations'!$A$1:$B$100,2,FALSE)"
Next i

End Sub
'-----------------------------------------------

This works reasonably well (and fast), with the exception that if an
explanation was longer than 255 characters it gets truncated. A person
on Mr Excel responded and suggested that I use the following code
instead:

'-----------------------------------------------
Sub Option2()

Application.ScreenUpdating = False

Dim c As Range
Dim StrToFind As String
Dim MyCount
MyCount = Range(Range("A5"), Range("A5").End(xlDown)).count + 4
'On Error GoTo ErrorOccurred

For i = 5 To MyCount
ThisWorkbook.Activate
Sheets(1).Activate
StrToFind = Cells(i, "C")

Workbooks.Open Filename:= _
"C:\My Documents\Survey\" & Cells(i, 1) & ".xls"
Sheets("Explanations").Select

Set c = Range("A1:A100").Find(What:=StrToFind, After:=Range("A1"),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False)

If Not c Is Nothing Then
ThisWorkbook.ActiveSheet.Cells(i, 15) = c.Offset(0, 1)
Else
ThisWorkbook.ActiveSheet.Cells(i, 15) = "Not found"
End If

ActiveWorkbook.Close (False)
Next i

Exit Sub

ErrorOccurred:
ThisWorkbook.ActiveSheet.Cells(i, 15) = "ERROR"
Resume Next

Application.ScreenUpdating = False

End Sub
'-----------------------------------------

I ran this macro for the sample of 20 records that had more than 255
characters and only one resulted in having an error
"Run-time error '1004': Application-defined or object-defined
error"
(I found this record had 1,104 characters in the explanation)
What does this error mean?

In addition, when i ran the macro for all 424 records, it took
extremely long. After 15 minutes I hit escape and found that only 100
observations were processed.

Does anyone have any ideas to solve my problem? Can code be added to
either my option1 or option2 macros to have this run more
efficiently... or should i start from scratch? Please help! THANKS...