Split data Macro

[Marcus Ostman]

Hi!
Can anyone tell me whats wrong with these two macros? Or help me with
a better one. Im trying to split the data in all cells in one column,
both these macros only work on the first row.

/Marcus

The data im trying to split:
Q1517A#ABB
C5686B#ABB
C5687C#ABB
350544-B21
344257-B21


Sub Split1()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," _
& Selection.Address).SpecialCells(xlCellTypeConstants,
xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
ActiveCell.Offset(0, 1) = Left(ActiveCell, _
Application.WorksheetFunction.Find("#", ActiveCell) - 1)
ActiveCell.Offset(0, 2) = Right(ActiveCell, _
Application.WorksheetFunction.Find("#", ActiveCell) - 3)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sub Split2()
For Each c In Columns(ActiveCell.Column).Cells 'Alla rader i aktuell
column
If Not IsEmpty(c.Value) Then
ActiveCell.Offset(0, 1) = Left(ActiveCell, _
Application.WorksheetFunction.Find("#", ActiveCell) - 1)
ActiveCell.Offset(0, 2) = Right(ActiveCell, _
Application.WorksheetFunction.Find("#", ActiveCell) - 3)
End If
Next c
End Sub

 

[Frank Kabel]

Hi
and how do you want to split (use the '#' as separator?)

 

[Dave Peterson]

When you do this:

For Each c In Columns(ActiveCell.Column).Cells
It looks like you want your program to loop through all the cells in that range
(and it's a big range!).

But the next line:
ActiveCell.Offset(0, 1) = Left(ActiveCell,....

You refer to the activecell. And you're always referring to that activecell.

Maybe a better explanation:
C changes to the next cell, but you don't do anything with it. You're still
looking at the activecell.

Option Explicit
Sub Split2A()
Dim myCell As Range
Dim PoundPos As Long

With ActiveSheet
For Each myCell In Intersect(.UsedRange, ActiveCell.EntireColumn).Cells
If Not IsEmpty(myCell.Value) Then
PoundPos = InStr(1, myCell.Value, "#", vbTextCompare)
If PoundPos > 0 Then
myCell.Offset(0, 1).Value = Left(myCell.Value, PoundPos - 1)
myCell.Offset(0, 2).Value = Mid(myCell.Value, PoundPos)
End If
End If
Next myCell
End With
End Sub

I'm not sure if I kept the parts you wanted to keep. I just split it into two
pieces--everything up to the # and then from the # to the end.

But maybe it'll get you going.

===
Instr() is VBA's equivalent of application.Find.
and by limiting the code to just the portion of the column in the usedrange, it
doesn't have to check all 65536 cells.

You could modify the first routine to do the same kind of thing (the activecell
stuff needs to go).