Error when using selection/text to columns

T

Tom Walat

Having a little difficulty here. Using Excel 2000/WinNT4.
The macro below looks for a particular phrase on a worksheet,
when it finds it the second time, it selects that cell and the cell
below it.
I then want to run a TextToColumns on that selection.
Instead I get:
Run-time error '1004'
Method 'Range' of object '_Global' failed

Thanks for any help.
- Tom

*******************************************
Sub aaaaFormatTotalsLine()
Range("A1").Select
Cells.Find(What:="TOWN", After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Cells.Find(What:="TOWN", After:=ActiveCell, LookIn:=xlValues,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
' SELECT CONSECUTIVE CELLS
Range(ActiveCell, ActiveCell.Offset(1, 0)).Select
' CONVERT TEXT TO COLUMNS
Selection.TextToColumns Destination:=Range(Selection),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(21, 1), Array(27, 1),
Array(35, 1), Array(43, 1))
End Sub
 
J

JE McGimpsey

One way:

Public Sub aaaaaFormatTotalsLine()
Dim rFound As Range
Dim sFoundAddr As String
Set rFound = Cells.Find( _
What:="TOWN", _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rFound Is Nothing Then
sFoundAddr = rFound.Address
Set rFound = Cells.FindNext(After:=rFound)
If Not rFound.Address = sFoundAddr Then
With rFound.Resize(2, 1)
.TextToColumns _
Destination:=.Cells, _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), _
Array(21, 1), Array(27, 1), _
Array(35, 1), Array(43, 1))
End With
End If
End If
End Sub

It's almost never necessary to select or activate a range you're working
on. Using the range object directly makes your code smaller, faster,
and, IMO, easier to maintain.
 
T

Tom Walat

Thanks, JE!
That really is a nice bit of coding. I appreciate the tip and the
prompt reply.
- Tom
 

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