Why am I losing my Range object?

[Ed from AZ]

This code was originally written in Excel 200 - I am now in Excel
2003. The code pops up a UserForm and asks for keywords, then does a
text search through a collection of Word documents. The original code
wrote the full path of all matching documents into the worksheet as
hyperlinks. I'm trying to re-write the code to simply write the names
as text formatting as hyperlinks (I'll use the SelectionChange event
to capture a click and do something else).

Somehow, I keep losing my rngFiles range object near the end of the
code. At least, that's what I think the 424 - object required error
is trying to tell me. I have chaged the With - End blocks and stuck
Application.FileSearch everywhere to avoid confusion (then again maybe
not!!). If anyone can help me over this hump, I'd greatly appreciate
it.

Ed

With Application.FileSearch
.NewSearch
.LookIn = strLocation
.SearchSubFolders = False
.TextOrProperty = strSearchFor
.Filename = "L5-" & strName & "*.doc"
.Execute
End With
Stop
Set rngFiles = Range("E20")
rngFiles.Offset(0, 0) = "Found " &
Application.FileSearch.FoundFiles.Count & " containing " &
Application.FileSearch.TextOrProperty
For lngIndex = 1 To Application.FileSearch.FoundFiles.Count
'ActiveSheet.Hyperlinks.Add Anchor:=rngFiles.Offset(lngIndex,
0), Address:=.FoundFiles.Item(lngIndex)
strName = Application.FileSearch.FoundFiles.Item(lngIndex)

' *************** ERROR NEXT LINE
rngFiles.Offset(lngIndex, 0).Text = strName
' **************** ERROR PREV LINE

rngFiles.Offset(lngIndex, 0).Font.ColorIndex = 5
rngFiles.Offset(lngIndex, 0).Font.Underline =
xlUnderlineStyleSingle
Next

End Sub

 

[Mark Ivey]

You are setting the text for your range position to whatever you have loaded
into "strName".

Maybe you should have this item reversed...

strName = rngFiles.Offset(lngIndex, 0).Text



Mark Ivey

 

[Bernie Deitrick]

Ed,

You cannot set the text property of a range object - use .Value instead to
set the value. A range's .Text property returns the formatted value of a
range object's value but it is read only. (For example, Activecell.Text for
a formatted date cell will return "4/22/2008" instead of the serial date
value.)

HTH,
Bernie
MS Excel MVP

 

[Ed from AZ]

Thank you, Bernie! That was it!!

I gotta get more sleep . . . . .

Ed