Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - concatenate with a loop
Hi guys
I have two macro's. one tocheck for a status and act accordingly and one to split a full name into three parts. View the code below: Sub ConsCodeV3() Dim c As Range For Each c In Range("E1:E20") If c < "CONS" Then c.Offset(, -3) = UCase(c.Offset(, -2)) c.Offset(, -4) = "ConsCode" End If If c = "CONS" Then c.Offset(, -3) = UCase(c.Offset(, -2)) End If Next c End Sub Sub NameSplit() Dim rng As Range For Each rng In Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row) Range(Cells(rng.Row, rng.Column + 10), Cells(rng.Row, rng.Column + 13)) = Split(rng, " ") Next rng End Sub Now what i want to do is the split names, i want to concatenate them in a specific way. I need to take the last name and truncate down to 4 letters, then add the initials of the first name and any other initials remainging. This then needs to go into Column A. this will only want to happen for those that have "ConsCode" in column A following the ConsCodeV3() macro. Is this easily done? Submitted via EggHeadCafe - Software Developer Portal of Choice ..NET - Writing a self-updating application in C# http://www.eggheadcafe.com/tutorials...a-selfupd.aspx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - concatenate with a loop
Use a function and pass the variables that are required. In the function set the return value back to to function name. Sub ConsCodeV3() Dim c As Range For Each c In Range("E1:E20") If c < "CONS" Then c.Offset(, -3) = UCase(c.Offset(, -2)) ColB = c.offset(,-3) ColL = c.offset(,+7) ColO = c.offset(,+10) c.Offset(, -4) = NameSplit(ColB,ColL,ColO) End If If c = "CONS" Then c.Offset(, -3) = UCase(c.Offset(, -2)) End If Next c End Sub Function NameSplit(ColB,ColL,ColO) Dim rng As Range NameSplit = left(ColB,4) & Coll & ColO End Function -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=170411 Microsoft Office Help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Concatenate Loop | Excel Discussion (Misc queries) | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel |