Hi everyone

I have the following problem. As you can see in the picture below
have 2 columns.

[image: http://i54.photobucket.com/albums/g115/TomBP/Town.jpg]

Now what I want my macro to do is what you see in the next pictu

[image: http://i54.photobucket.com/albums/g1...BP/Postal.jpg]

There is a colum that has been inserted. In this column, which is name
Postal, the postal codes from the previous town column are pasted.

Now the hard part is that the postal codes aren't always numbers. The
can look like CH542 aswell. The only thing that is the same for th
town column is that there is a space between the town and the posta
code.

I hope you understand what I mean and thanks in advance

--
TomB
-----------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...fo&userid=3611
View this thread: http://www.excelforum.com/showthread.php?threadid=56065

Sub splitColumns()
Columns("E").Select
Selection.Insert Shift:=xlToRight
For each sell in sheets
Do
If activecell = "" then
end sub
else
end if
Range("D9").Activate
x = InStr(ActiveCell, " ")

ActiveCell.Offset(0, 1) = Mid(ActiveCell.Offset(0, 0), x, 8)
Activecell.offset(1,0)
Loop while activecell < ""
Next
End Sub
--
Best wishes,

Jim


"TomBP" wrote:


Hi everyone

I have the following problem. As you can see in the picture below I
have 2 columns.

[image: http://i54.photobucket.com/albums/g115/TomBP/Town.jpg]

Now what I want my macro to do is what you see in the next pictu

[image: http://i54.photobucket.com/albums/g1...BP/Postal.jpg]

There is a colum that has been inserted. In this column, which is named
Postal, the postal codes from the previous town column are pasted.

Now the hard part is that the postal codes aren't always numbers. They
can look like CH542 aswell. The only thing that is the same for the
town column is that there is a space between the town and the postal
code.

I hope you understand what I mean and thanks in advance.


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

Jim,

I tried using your macro but it gave the following error:

[image: http://i54.photobucket.com/albums/g1.../errorsub.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

My BAD!

The "End Sub" after the "If - Else" should be "Exit Sub". I typed this out
instead of copying from actual code which I should have done.

Sorry about that.
--
Best wishes,

Jim


"TomBP" wrote:


Jim,

I tried using your macro but it gave the following error:

[image: http://i54.photobucket.com/albums/g1.../errorsub.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

No problem. I changed it to Exit Sub.

But...

Another error pops up

[image: http://i54.photobucket.com/albums/g1...ntaxerror.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

I must have been half asleep when I sent the original reply. Add .Activate
to the end of that and it will work.

Sorry again.
--
Best wishes,

Jim


"TomBP" wrote:


No problem. I changed it to Exit Sub.

But...

Another error pops up

[image: http://i54.photobucket.com/albums/g1...ntaxerror.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

The complete line would be Activecell.Offset(0,1).Activate

Sorry about the confusion.
--
Best wishes,

Jim


"Jim Jackson" wrote:

I must have been half asleep when I sent the original reply. Add .Activate
to the end of that and it will work.

Sorry again.
--
Best wishes,

Jim


"TomBP" wrote:


No problem. I changed it to Exit Sub.

But...

Another error pops up

[image: http://i54.photobucket.com/albums/g1...ntaxerror.jpg]


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

The macro works now but it doesn't do what I had in mind. It justs
inserts a column. The thing I wanted extra was that the postal codes in
the town column would be cut and pasted into the inserted column.

Thx for your time tho


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659

Tom,

This routine will do what you need. Just change cell references as needed.

Sub splitColumns()
' Inserts the column
Sheets("Sheet1").Select
ActiveSheet.Columns("E").Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(0, -1).Activate

'Moves the mail code
For Each cell In Sheets
Do
If ActiveCell = "" Then
Exit Sub
Else
End If
x = InStr(ActiveCell, " ")
ActiveCell.Offset(0, 1) = Mid(ActiveCell.Offset(0, 0), x, 8)
ActiveCell = Mid(ActiveCell, 1, x)
ActiveCell.Offset(1, 0).Activate
Loop While ActiveCell < ""
Next
End Sub
--
Best wishes,

Jim


"TomBP" wrote:


The macro works now but it doesn't do what I had in mind. It justs
inserts a column. The thing I wanted extra was that the postal codes in
the town column would be cut and pasted into the inserted column.

Thx for your time tho


--
TomBP
------------------------------------------------------------------------
TomBP's Profile: http://www.excelforum.com/member.php...o&userid=36112
View this thread: http://www.excelforum.com/showthread...hreadid=560659