Why am I losing my Range object?
 

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

Why am I losing my Range object?
 

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


"Ed from AZ" wrote in message
...
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

Why am I losing my Range object?
 

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" wrote in message
...
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

Why am I losing my Range object?
 

Thank you, Bernie! That was it!!

I gotta get more sleep . . . . .

Ed


On Apr 22, 8:29*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in ...



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- Hide quoted text -

- Show quoted text -