The data is in colA, of the type 9 1A going up to
(say) 999 999Z.

I would like to loop through that range and split the
data into 3 columns.

Here's where I'm so far:

Sub Split_ColA()

Dim x As Variant, y As Variant, z As Variant, ws As Worksheet, FirstRw As
Long
Dim LastRw As Long, C As Range, PageRef As Range

With ActiveSheet
'Insert new columns
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 3
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 4
.Columns("A:B").NumberFormat = "0"
'Find the PageRef range
FirstRw = .Range("C2").Row
LastRw = .Range("C65536").End(xlUp).Row
'and set it
Set PageRef = .Range("C" & FirstRw, "C" & LastRw)
For Each C In PageRef
If Not IsEmpty(C) Then
x = (Split(C, " "))
y = Right(x(2), 1)
z = Left(x(2), 1)
C.Offset(0, -2).Value = x(0)
C.Offset(0, -1).Value = z
C.Value = y
End If
Next
End With

This will not deal with 999 10A giving a Subscript out of range
error, so my Left statement must be wrong....Can't see why?

Incidentally, have now found rare occurrences where the data
can be 999 10A ie 2 spaces
rather than the more normal 999 10A ie 1 space
hence my Split approach

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004

My mistake........
for the data: 999 10A the error is on the line
y = Right(x(2), 1)

Apologies.

"Stuart" wrote in message
...
The data is in colA, of the type 9 1A going up to
(say) 999 999Z.

I would like to loop through that range and split the
data into 3 columns.

Here's where I'm so far:

Sub Split_ColA()

Dim x As Variant, y As Variant, z As Variant, ws As Worksheet, FirstRw As
Long
Dim LastRw As Long, C As Range, PageRef As Range

With ActiveSheet
'Insert new columns
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 3
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 4
.Columns("A:B").NumberFormat = "0"
'Find the PageRef range
FirstRw = .Range("C2").Row
LastRw = .Range("C65536").End(xlUp).Row
'and set it
Set PageRef = .Range("C" & FirstRw, "C" & LastRw)
For Each C In PageRef
If Not IsEmpty(C) Then
x = (Split(C, " "))
y = Right(x(2), 1)
z = Left(x(2), 1)
C.Offset(0, -2).Value = x(0)
C.Offset(0, -1).Value = z
C.Value = y
End If
Next
End With

This will not deal with 999 10A giving a Subscript out of range
error, so my Left statement must be wrong....Can't see why?

Incidentally, have now found rare occurrences where the data
can be 999 10A ie 2 spaces
rather than the more normal 999 10A ie 1 space
hence my Split approach

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004

Sub Split_ColA()

Dim x As Variant, y As Variant, z As Variant, ws As Worksheet
Dim FirstRw As Long, sStr as String
Dim LastRw As Long, C As Range, PageRef As Range

With ActiveSheet
'Insert new columns
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 3
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 4
.Columns("A:B").NumberFormat = "0"
'Find the PageRef range
FirstRw = .Range("C2").Row
LastRw = .Range("C65536").End(xlUp).Row
'and set it
Set PageRef = .Range("C" & FirstRw, "C" & LastRw)
For Each C In PageRef
If Not IsEmpty(C) Then
' take care of the double space
sStr = Application.Trim(c.value)
x = (Split(sStr, " "))
y = Right(x(1), 1)
z = Left(x(1), len(x(1))-1)
C.Offset(0, -2).Value = x(0)
C.Offset(0, -1).Value = z
C.Value = y
End If
Next
End With

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
My mistake........
for the data: 999 10A the error is on the line
y = Right(x(2), 1)

Apologies.

"Stuart" wrote in message
...
The data is in colA, of the type 9 1A going up to
(say) 999 999Z.

I would like to loop through that range and split the
data into 3 columns.

Here's where I'm so far:

Sub Split_ColA()

Dim x As Variant, y As Variant, z As Variant, ws As Worksheet, FirstRw
As
Long
Dim LastRw As Long, C As Range, PageRef As Range

With ActiveSheet
'Insert new columns
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 3
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 4
.Columns("A:B").NumberFormat = "0"
'Find the PageRef range
FirstRw = .Range("C2").Row
LastRw = .Range("C65536").End(xlUp).Row
'and set it
Set PageRef = .Range("C" & FirstRw, "C" & LastRw)
For Each C In PageRef
If Not IsEmpty(C) Then
x = (Split(C, " "))
y = Right(x(2), 1)
z = Left(x(2), 1)
C.Offset(0, -2).Value = x(0)
C.Offset(0, -1).Value = z
C.Value = y
End If
Next
End With

This will not deal with 999 10A giving a Subscript out of range
error, so my Left statement must be wrong....Can't see why?

Incidentally, have now found rare occurrences where the data
can be 999 10A ie 2 spaces
rather than the more normal 999 10A ie 1 space
hence my Split approach

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004

Tom,

many thanks....amongst many other things, forgot
'Trim'.

Regards.

"Tom Ogilvy" wrote in message
...
Sub Split_ColA()

Dim x As Variant, y As Variant, z As Variant, ws As Worksheet
Dim FirstRw As Long, sStr as String
Dim LastRw As Long, C As Range, PageRef As Range

With ActiveSheet
'Insert new columns
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 3
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 4
.Columns("A:B").NumberFormat = "0"
'Find the PageRef range
FirstRw = .Range("C2").Row
LastRw = .Range("C65536").End(xlUp).Row
'and set it
Set PageRef = .Range("C" & FirstRw, "C" & LastRw)
For Each C In PageRef
If Not IsEmpty(C) Then
' take care of the double space
sStr = Application.Trim(c.value)
x = (Split(sStr, " "))
y = Right(x(1), 1)
z = Left(x(1), len(x(1))-1)
C.Offset(0, -2).Value = x(0)
C.Offset(0, -1).Value = z
C.Value = y
End If
Next
End With

--
Regards,
Tom Ogilvy


"Stuart" wrote in message
...
My mistake........
for the data: 999 10A the error is on the line
y = Right(x(2), 1)

Apologies.

"Stuart" wrote in message
...
The data is in colA, of the type 9 1A going up to
(say) 999 999Z.

I would like to loop through that range and split the
data into 3 columns.

Here's where I'm so far:

Sub Split_ColA()

Dim x As Variant, y As Variant, z As Variant, ws As Worksheet, FirstRw
As
Long
Dim LastRw As Long, C As Range, PageRef As Range

With ActiveSheet
'Insert new columns
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 3
.Columns("A:A").EntireColumn.Insert
.Columns("A:A").ColumnWidth = 4
.Columns("A:B").NumberFormat = "0"
'Find the PageRef range
FirstRw = .Range("C2").Row
LastRw = .Range("C65536").End(xlUp).Row
'and set it
Set PageRef = .Range("C" & FirstRw, "C" & LastRw)
For Each C In PageRef
If Not IsEmpty(C) Then
x = (Split(C, " "))
y = Right(x(2), 1)
z = Left(x(2), 1)
C.Offset(0, -2).Value = x(0)
C.Offset(0, -1).Value = z
C.Value = y
End If
Next
End With

This will not deal with 999 10A giving a Subscript out of range
error, so my Left statement must be wrong....Can't see why?

Incidentally, have now found rare occurrences where the data
can be 999 10A ie 2 spaces
rather than the more normal 999 10A ie 1 space
hence my Split approach

Regards.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.577 / Virus Database: 366 - Release Date: 03/02/2004