If your values in C4:Cxxx are always text (no formulas), you could do:

Select the range
data|text to columns
fixed width
draw a line after the 4th character (and remove any other lines)
Do not import the first field
and import the second field as text
And change the destination to be on column to the right.

In code:

Option Explicit
Sub AccountType2()
Dim myRng As Range
Dim wks As Worksheet
Dim TopCell As Range
Dim BotCell As Range

Set wks = ActiveSheet

With wks
Set TopCell = .Range("C4")
Set BotCell = TopCell
If IsEmpty(TopCell.Offset(1, 0)) Then
Set BotCell = TopCell
ElseIf IsEmpty(TopCell.Offset(2, 0)) Then
Set BotCell = TopCell.Offset(1, 0)
Else
Set BotCell = TopCell.End(xlDown)
End If

.Range(TopCell, BotCell).TextToColumns _
Destination:=TopCell.Offset(0, 1), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(4, 2))
End With

End Sub

wrote:

On Apr 19, 11:11 am, JE McGimpsey wrote:
There is no Sheet object. Use

Dim s1 As Worksheet

In article . com,



wrote:
Thx. Tried this & I get Compile error: User-defined type not defined
for: s1 As Sheet- Hide quoted text -

- Show quoted text -

Thanks. That fixed it--though it runs a little slow.

--

Dave Peterson