S
Stuart
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.
(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.