Why am I losing my Range object?

E

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
 
M

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
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top