Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - splitting name and re building in specific order
Hi Guys
sorry if im in the wrong section when asking this, if i am please let me know and i will re post. I have a spreadsheet with the following columns: Code Name - in uppercase Name - all lower case Title Status CRI Status The code column is generated by the 4 first characters of the last name and any initials of the previous names. The Name in upper case is generated from the name in lower case. The name columns have the name in Full such as Mr A J Cook. I do several other things with the sheet as well which i can do easily but im stuck when it comes to creating the Code field. I used 'Text to Columns' feature to split the full name into 3 columns. so Mr A J Cook becomes A | J | Cook (| = only to show you they are in seperate columns) I can use the Concatenate feature to pull the fields to together to make COOkAJ but i have a problem when it comes to names that only have one initial. This puts the name only into two columns and makes the concatenation pull through in the wrong order. So what i wanted to do is use a Loop to check the third column for any data. If it finds anything in the third (which would be Cook in this example) it concatenates in the following order... 3+1+2 = code else i want it to concatenate in this order ... 2+1 = code Once it has concatendated it needs to paste into Cell A1 and then move down to cell A2 to run the same loop again until all is done It then needs to loop until it has no information in all three columns (which would assume it was done) i really hope that made sense... Adam Submitted via EggHeadCafe - Software Developer Portal of Choice Forms Based Authentication Filtered Content Editor for SharePoint http://www.eggheadcafe.com/tutorials...enticatio.aspx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - splitting name and re building in specific order
You need a simple If statement. I'm not sure of you columns so I'm using X,Y,Z. Change as required. I'm also using Column B to determin when the data is finised RowCount = 1 Do while Range("B" & RowCount) < "" if Range("Z" & Rowcount) = "" then Range("A" & RowCount) = Range("Z" & RowCount) & _ Range("X" & RowCount) & Range("Y" & RowCount) else Range("A" & RowCount) = Range("Y" & RowCount) & _ Range("X" & RowCount) end if RowCount = RowCount + 1 loop -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168752 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - splitting name and re building in specific order
On Mon, 11 Jan 2010 02:26:15 -0800, adam cook wrote:
Hi Guys sorry if im in the wrong section when asking this, if i am please let me know and i will re post. I have a spreadsheet with the following columns: Code Name - in uppercase Name - all lower case Title Status CRI Status The code column is generated by the 4 first characters of the last name and any initials of the previous names. The Name in upper case is generated from the name in lower case. The name columns have the name in Full such as Mr A J Cook. I do several other things with the sheet as well which i can do easily but im stuck when it comes to creating the Code field. I used 'Text to Columns' feature to split the full name into 3 columns. so Mr A J Cook becomes A | J | Cook (| = only to show you they are in seperate columns) I can use the Concatenate feature to pull the fields to together to make COOkAJ but i have a problem when it comes to names that only have one initial. This puts the name only into two columns and makes the concatenation pull through in the wrong order. So what i wanted to do is use a Loop to check the third column for any data. If it finds anything in the third (which would be Cook in this example) it concatenates in the following order... 3+1+2 = code else i want it to concatenate in this order ... 2+1 = code Once it has concatendated it needs to paste into Cell A1 and then move down to cell A2 to run the same loop again until all is done It then needs to loop until it has no information in all three columns (which would assume it was done) i really hope that made sense... Adam There's no need to do the Text-to-Columns to generate the code. How do you get rid of the title (e.g. Mr) when you do the text-to-columns? Do all names have a title, or do you have to test for it? You could use something like the code below to generate the code. See comments in line for how to handle the Title: ======================================= Option Explicit Function NameCode(FullName As String) As String Dim aName As Variant Dim i As Long aName = Split(WorksheetFunction.Trim(UCase(FullName))) NameCode = Left(aName(UBound(aName)), 4) 'start loop at 1 if there is a Title 'otherwise start loop at 0 For i = 1 To UBound(aName) - 1 NameCode = NameCode & Left(aName(i), 1) Next i End Function ====================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specific sort order | Excel Discussion (Misc queries) | |||
Arrange sheet in specific order | Excel Programming | |||
Have 2 Macros run in a specific order...Can This Be Done | Excel Programming | |||
Have 2 Macros run in a specific order...Can This Be Done | Excel Programming | |||
print specific worksheets in specific order. | Excel Programming |