Im trying to write a program where a alphanumeric is typed into a cell A1

(ex. 2345n2375n2938n6475n2938)

Once the macro reads this i need the macro to break this down using "n" as a delimiter and out put into B1-5
then automatically return to a1 clear the data in the cell and wait for another number to be inputed

the output needs to look like this like this

b1 contains 2345
b2 contains 2375
b3 contains 2938
b4 contains 6475
b5 contains 2938

On Fri, 10 Aug 2012 18:05:17 -0700 (PDT), GammaRuit wrote:

Im trying to write a program where a alphanumeric is typed into a cell A1

(ex. 2345n2375n2938n6475n2938)

Once the macro reads this i need the macro to break this down using "n" as a delimiter and out put into B1-5
then automatically return to a1 clear the data in the cell and wait for another number to be inputed

the output needs to look like this like this

b1 contains 2345
b2 contains 2375
b3 contains 2938
b4 contains 6475
b5 contains 2938

Here's one way with an event-triggered macro

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.

====================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, r1 As Range
Dim v As Variant
Set r1 = Range("a1")
Application.EnableEvents = False
If Not Intersect(r1, Target) Is Nothing Then
If Len(r1.Text) 0 Then
v = Split(r1.Text, "n")
Set r = r1.Offset(columnoffset:=1).Resize(rowsize:=UBound( v) - LBound(v) + 1)
r.EntireColumn.ClearContents
r = WorksheetFunction.Transpose(v)
r1.ClearContents
End If
End If
Application.EnableEvents = True
End Sub
========================

As written, the routine is case sensitive. For a case insensitive version, add the line:

Option Compare Text

at the very beginning, above the Private Sub .... line.