Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Further Split-type help, please
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove split a split window? | New Users to Excel | |||
Excel will not let me type any characters....I can type in Word. | Excel Discussion (Misc queries) | |||
Excel will not let me type any characters....I can type in Word. | Excel Discussion (Misc queries) | |||
how to change all the words of one type(Gunsuh type)to another | Excel Discussion (Misc queries) | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) |