Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Consecutive Numbers in the Middle Of Each Cell in a Selecti
Hello,
I work with lists that look like this: How To Build A Chair - Getting Started How To Build A Chair - The Hard Part How To Build A Chair - Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - Breakfast Jack's Favorite Foods - Snacks Jack's Favorite Foods - Drinks Jack's Favorite Foods - Picnics I want to build a macro that will allow me to select all the cells from the same book, run the macro, and end up with this: How To Build A Chair - 1. Getting Started How To Build A Chair - 2. The Hard Part How To Build A Chair - 3. Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - 1. Breakfast Jack's Favorite Foods - 2. Snacks Jack's Favorite Foods - 3. Drinks Jack's Favorite Foods - 4. Picnics I've recorded and written macros before, but never with any of the kinds of variables and loops I think i'll need here. Any suggestions, advice or links will be greatly appreciated. Warmly, CitizenKate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Consecutive Numbers in the Middle Of Each Cell in a Selecti
Try code like the following. Change the line
Set R = Range("A1") '<<< Change to start cell to reference the cell in which your text begins. The code will scan down until a blank cell is encountered. Sub AAAA() Dim R As Range Dim N As Long Dim Pos As Long Dim S As String Dim S2 As String Dim T As String Set R = Range("A1") '<<< Change to start cell Do Until R.Value = vbNullString Pos = InStr(1, R.Value, "-", vbBinaryCompare) If Pos 0 Then N = N + 1 S = Left(R.Value, Pos + 1) T = Mid(R.Value, Pos + 2) Pos = InStr(1, R(2, 1).Value, "-", vbBinaryCompare) If Pos 0 Then S2 = Left(R(2, 1).Value, Pos + 1) If StrComp(S, S2, vbTextCompare) = 0 Then R.Value = S & CStr(N) & " " & T Else If N 0 Then Pos = InStr(1, R(2, 1).Value, "-", vbBinaryCompare) If Pos 0 Then S = Left(R(2, 1).Value, Pos + 1) T = Mid(R(2, 1).Value, Pos + 2) R(2, 1).Value = S & CStr(N) & " " & T End If End If N = 0 End If Else Pos = InStr(1, R.Value, "-", vbBinaryCompare) If Pos 0 Then S = Left(R.Value, Pos + 1) T = Mid(R.Value, Pos + 2) R.Value = S & CStr(N) & " " & T End If N = 0 End If End If Set R = R(2, 1) Loop End Sub Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 5 Jan 2010 15:34:02 -0800, CitizenKate wrote: Hello, I work with lists that look like this: How To Build A Chair - Getting Started How To Build A Chair - The Hard Part How To Build A Chair - Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - Breakfast Jack's Favorite Foods - Snacks Jack's Favorite Foods - Drinks Jack's Favorite Foods - Picnics I want to build a macro that will allow me to select all the cells from the same book, run the macro, and end up with this: How To Build A Chair - 1. Getting Started How To Build A Chair - 2. The Hard Part How To Build A Chair - 3. Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - 1. Breakfast Jack's Favorite Foods - 2. Snacks Jack's Favorite Foods - 3. Drinks Jack's Favorite Foods - 4. Picnics I've recorded and written macros before, but never with any of the kinds of variables and loops I think i'll need here. Any suggestions, advice or links will be greatly appreciated. Warmly, CitizenKate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Consecutive Numbers in the Middle Of Each Cell in a Selecti
Select the range to be converted and try the below macro;
Sub Macro() Dim arrData As Variant, strData As String, intCount As Integer For Each cell In Selection If InStr(cell, "-") Then arrData = Split(cell, "-") If strData < arrData(0) Then intCount = 1 arrData(1) = intCount & "." & arrData(1) cell.Value = Join(arrData, "-") strData = arrData(0) intCount = intCount + 1 End If Next End Sub -- Jacob "CitizenKate" wrote: Hello, I work with lists that look like this: How To Build A Chair - Getting Started How To Build A Chair - The Hard Part How To Build A Chair - Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - Breakfast Jack's Favorite Foods - Snacks Jack's Favorite Foods - Drinks Jack's Favorite Foods - Picnics I want to build a macro that will allow me to select all the cells from the same book, run the macro, and end up with this: How To Build A Chair - 1. Getting Started How To Build A Chair - 2. The Hard Part How To Build A Chair - 3. Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - 1. Breakfast Jack's Favorite Foods - 2. Snacks Jack's Favorite Foods - 3. Drinks Jack's Favorite Foods - 4. Picnics I've recorded and written macros before, but never with any of the kinds of variables and loops I think i'll need here. Any suggestions, advice or links will be greatly appreciated. Warmly, CitizenKate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Consecutive Numbers in the Middle Of Each Cell in a Sel
Holy Cow, Jacob! This so short and simple-looking. I'm not anywhere near the
skill level to have come up with this. I've learned a lot just looking at it. A total eye-opener. Thank you so much. Kate "Jacob Skaria" wrote: Select the range to be converted and try the below macro; Sub Macro() Dim arrData As Variant, strData As String, intCount As Integer For Each cell In Selection If InStr(cell, "-") Then arrData = Split(cell, "-") If strData < arrData(0) Then intCount = 1 arrData(1) = intCount & "." & arrData(1) cell.Value = Join(arrData, "-") strData = arrData(0) intCount = intCount + 1 End If Next End Sub -- Jacob "CitizenKate" wrote: Hello, I work with lists that look like this: How To Build A Chair - Getting Started How To Build A Chair - The Hard Part How To Build A Chair - Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - Breakfast Jack's Favorite Foods - Snacks Jack's Favorite Foods - Drinks Jack's Favorite Foods - Picnics I want to build a macro that will allow me to select all the cells from the same book, run the macro, and end up with this: How To Build A Chair - 1. Getting Started How To Build A Chair - 2. The Hard Part How To Build A Chair - 3. Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - 1. Breakfast Jack's Favorite Foods - 2. Snacks Jack's Favorite Foods - 3. Drinks Jack's Favorite Foods - 4. Picnics I've recorded and written macros before, but never with any of the kinds of variables and loops I think i'll need here. Any suggestions, advice or links will be greatly appreciated. Warmly, CitizenKate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Consecutive Numbers in the Middle Of Each Cell in a Sel
Thanks for the feedback. You are most welcome.
-- Jacob "CitizenKate" wrote: Holy Cow, Jacob! This so short and simple-looking. I'm not anywhere near the skill level to have come up with this. I've learned a lot just looking at it. A total eye-opener. Thank you so much. Kate "Jacob Skaria" wrote: Select the range to be converted and try the below macro; Sub Macro() Dim arrData As Variant, strData As String, intCount As Integer For Each cell In Selection If InStr(cell, "-") Then arrData = Split(cell, "-") If strData < arrData(0) Then intCount = 1 arrData(1) = intCount & "." & arrData(1) cell.Value = Join(arrData, "-") strData = arrData(0) intCount = intCount + 1 End If Next End Sub -- Jacob "CitizenKate" wrote: Hello, I work with lists that look like this: How To Build A Chair - Getting Started How To Build A Chair - The Hard Part How To Build A Chair - Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - Breakfast Jack's Favorite Foods - Snacks Jack's Favorite Foods - Drinks Jack's Favorite Foods - Picnics I want to build a macro that will allow me to select all the cells from the same book, run the macro, and end up with this: How To Build A Chair - 1. Getting Started How To Build A Chair - 2. The Hard Part How To Build A Chair - 3. Cleaning Up Forgotten Tales From Nova Scotia When To Say No Jack's Favorite Foods - 1. Breakfast Jack's Favorite Foods - 2. Snacks Jack's Favorite Foods - 3. Drinks Jack's Favorite Foods - 4. Picnics I've recorded and written macros before, but never with any of the kinds of variables and loops I think i'll need here. Any suggestions, advice or links will be greatly appreciated. Warmly, CitizenKate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecti Page in Userform by Name not Number | Excel Programming | |||
Insert rows in the middle of the document??? | Excel Programming | |||
Insert Cell Contents in middle of web address | Excel Discussion (Misc queries) | |||
Consecutive numbers in 1 cell | Excel Worksheet Functions | |||
Possible to insert cell data in the middle of line of text? | Excel Worksheet Functions |