Posted to microsoft.public.excel.programming
|
|
Further Split-type help, please
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
|