Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Creating Auto Separation of Numbers thru Defining Delimiter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Creating Auto Separation of Numbers thru Defining Delimiter
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parse delimiter is change from text to numbers | Excel Discussion (Misc queries) | |||
separation of text and numbers in an unmerged cell | Excel Discussion (Misc queries) | |||
defining names in a macro | Excel Discussion (Misc queries) | |||
Need importing macro to recognize #*# as delimiter | Excel Programming | |||
Defining an auto-open macro that prints specific worksheets | Excel Programming |