Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
I got this code from this NG, I do not remember the authors name, but thanks
any how because it works quite well. I am failing to find a way to make this macro ignore all characters within brackets "()". Any help is be appreciated. '====== 'Change Text to Upper Case or Proper Case. See Also: 'Force Upper Case/Proper Case 'Excel has 2 built in functions for converting text to 'either UPPER CASE or Proper Case. The 2 functions that 'do this are shown below; '=UPPER(A1) '=PROPER(A1) 'These Excel functions work well when referring to cells 'that house the text. However, there are many instances 'when using the Worksheet Function approach is not practical. 'The Excel macro code below can be used to change existing 'text to either UPPER CASE or Proper Case. If you run the 'macro with only a single cell selected it will work on the 'entire Worksheet. If you run the macro with more than 1 'cell selected it will work on only your selection. 'The other settings that the StrConv Function take are 'shown below. See the Excel VBA help for specifics. Sub ConvertCase() Dim rAcells As Range, rLoopCells As Range Dim lReply As Long 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then ' Convert to Upper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbUpperCase) Next rLoopCells Else ' Convert to Proper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbProperCase) Next rLoopCells End If End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
The macro uses two (2) functions ... it either uses UPPER or Proper to change
the case. These functions will NOT address your concern. As in most cases, code could be written, but it would be a lil' bigger job than 'modifing' this macro. Sorry. "Rick S." wrote: I got this code from this NG, I do not remember the authors name, but thanks any how because it works quite well. I am failing to find a way to make this macro ignore all characters within brackets "()". Any help is be appreciated. '====== 'Change Text to Upper Case or Proper Case. See Also: 'Force Upper Case/Proper Case 'Excel has 2 built in functions for converting text to 'either UPPER CASE or Proper Case. The 2 functions that 'do this are shown below; '=UPPER(A1) '=PROPER(A1) 'These Excel functions work well when referring to cells 'that house the text. However, there are many instances 'when using the Worksheet Function approach is not practical. 'The Excel macro code below can be used to change existing 'text to either UPPER CASE or Proper Case. If you run the 'macro with only a single cell selected it will work on the 'entire Worksheet. If you run the macro with more than 1 'cell selected it will work on only your selection. 'The other settings that the StrConv Function take are 'shown below. See the Excel VBA help for specifics. Sub ConvertCase() Dim rAcells As Range, rLoopCells As Range Dim lReply As Long 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then ' Convert to Upper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbUpperCase) Next rLoopCells Else ' Convert to Proper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbProperCase) Next rLoopCells End If End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
I was affraid of that, as I have failed miserably.
Thanks for your input. -- Regards VBA.Newb.Confused XP Pro Office 2007 "ND Pard" wrote: The macro uses two (2) functions ... it either uses UPPER or Proper to change the case. These functions will NOT address your concern. As in most cases, code could be written, but it would be a lil' bigger job than 'modifing' this macro. Sorry. "Rick S." wrote: I got this code from this NG, I do not remember the authors name, but thanks any how because it works quite well. I am failing to find a way to make this macro ignore all characters within brackets "()". Any help is be appreciated. '====== 'Change Text to Upper Case or Proper Case. See Also: 'Force Upper Case/Proper Case 'Excel has 2 built in functions for converting text to 'either UPPER CASE or Proper Case. The 2 functions that 'do this are shown below; '=UPPER(A1) '=PROPER(A1) 'These Excel functions work well when referring to cells 'that house the text. However, there are many instances 'when using the Worksheet Function approach is not practical. 'The Excel macro code below can be used to change existing 'text to either UPPER CASE or Proper Case. If you run the 'macro with only a single cell selected it will work on the 'entire Worksheet. If you run the macro with more than 1 'cell selected it will work on only your selection. 'The other settings that the StrConv Function take are 'shown below. See the Excel VBA help for specifics. Sub ConvertCase() Dim rAcells As Range, rLoopCells As Range Dim lReply As Long 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then ' Convert to Upper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbUpperCase) Next rLoopCells Else ' Convert to Proper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbProperCase) Next rLoopCells End If End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
I'm afraid ND Pard was wrong... the modification to make that macro ignore
text inside of parentheses is relatively easy to do. Sub ConvertCase() Dim rAcells As Range, Dim rLoopCells As Range Dim X As Long Dim lReply As Long Dim TextLine As String Dim Parsed() As String 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE; 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub For Each rLoopCells In rAcells TextLine = Replace(rLoopCells, ")", "()") Parsed = Split(TextLine, "(") For X = 0 To UBound(Parsed) Step 2 Parsed(X) = StrConv(Parsed(X), IIf(lReply = vbYes, _ vbUpperCase, vbProperCase)) Next rLoopCells = Replace(Join(Parsed, "("), "()", ")") Next End Sub Rick "Rick S." wrote in message ... I was affraid of that, as I have failed miserably. Thanks for your input. -- Regards VBA.Newb.Confused XP Pro Office 2007 "ND Pard" wrote: The macro uses two (2) functions ... it either uses UPPER or Proper to change the case. These functions will NOT address your concern. As in most cases, code could be written, but it would be a lil' bigger job than 'modifing' this macro. Sorry. "Rick S." wrote: I got this code from this NG, I do not remember the authors name, but thanks any how because it works quite well. I am failing to find a way to make this macro ignore all characters within brackets "()". Any help is be appreciated. '====== 'Change Text to Upper Case or Proper Case. See Also: 'Force Upper Case/Proper Case 'Excel has 2 built in functions for converting text to 'either UPPER CASE or Proper Case. The 2 functions that 'do this are shown below; '=UPPER(A1) '=PROPER(A1) 'These Excel functions work well when referring to cells 'that house the text. However, there are many instances 'when using the Worksheet Function approach is not practical. 'The Excel macro code below can be used to change existing 'text to either UPPER CASE or Proper Case. If you run the 'macro with only a single cell selected it will work on the 'entire Worksheet. If you run the macro with more than 1 'cell selected it will work on only your selection. 'The other settings that the StrConv Function take are 'shown below. See the Excel VBA help for specifics. Sub ConvertCase() Dim rAcells As Range, rLoopCells As Range Dim lReply As Long 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then ' Convert to Upper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbUpperCase) Next rLoopCells Else ' Convert to Proper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbProperCase) Next rLoopCells End If End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
I do not understand how you did that, but it works great!!!
Thank you!!! -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: I'm afraid ND Pard was wrong... the modification to make that macro ignore text inside of parentheses is relatively easy to do. Sub ConvertCase() Dim rAcells As Range, Dim rLoopCells As Range Dim X As Long Dim lReply As Long Dim TextLine As String Dim Parsed() As String 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE; 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub For Each rLoopCells In rAcells TextLine = Replace(rLoopCells, ")", "()") Parsed = Split(TextLine, "(") For X = 0 To UBound(Parsed) Step 2 Parsed(X) = StrConv(Parsed(X), IIf(lReply = vbYes, _ vbUpperCase, vbProperCase)) Next rLoopCells = Replace(Join(Parsed, "("), "()", ")") Next End Sub Rick "Rick S." wrote in message ... I was affraid of that, as I have failed miserably. Thanks for your input. -- Regards VBA.Newb.Confused XP Pro Office 2007 "ND Pard" wrote: The macro uses two (2) functions ... it either uses UPPER or Proper to change the case. These functions will NOT address your concern. As in most cases, code could be written, but it would be a lil' bigger job than 'modifing' this macro. Sorry. "Rick S." wrote: I got this code from this NG, I do not remember the authors name, but thanks any how because it works quite well. I am failing to find a way to make this macro ignore all characters within brackets "()". Any help is be appreciated. '====== 'Change Text to Upper Case or Proper Case. See Also: 'Force Upper Case/Proper Case 'Excel has 2 built in functions for converting text to 'either UPPER CASE or Proper Case. The 2 functions that 'do this are shown below; '=UPPER(A1) '=PROPER(A1) 'These Excel functions work well when referring to cells 'that house the text. However, there are many instances 'when using the Worksheet Function approach is not practical. 'The Excel macro code below can be used to change existing 'text to either UPPER CASE or Proper Case. If you run the 'macro with only a single cell selected it will work on the 'entire Worksheet. If you run the macro with more than 1 'cell selected it will work on only your selection. 'The other settings that the StrConv Function take are 'shown below. See the Excel VBA help for specifics. Sub ConvertCase() Dim rAcells As Range, rLoopCells As Range Dim lReply As Long 'Set variable to needed cells If Selection.Cells.Count = 1 Then Set rAcells = ActiveSheet.UsedRange Else Set rAcells = Selection End If On Error Resume Next 'In case of NO text constants. 'Set variable to all text constants Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues) If rAcells Is Nothing Then MsgBox "Could not find any text." On Error GoTo 0 Exit Sub End If lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _ vbYesNoCancel, "OzGrid.com") If lReply = vbCancel Then Exit Sub If lReply = vbYes Then ' Convert to Upper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbUpperCase) Next rLoopCells Else ' Convert to Proper Case For Each rLoopCells In rAcells rLoopCells = StrConv(rLoopCells, vbProperCase) Next rLoopCells End If End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
I do not understand how you did that,
If you are familiar with VBA, do you want an explanation? but it works great!!! I'm glad it worked out for you (I was a little worried that ND Pard's response might have kept you from looking back into this thread in order to see it). Rick |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
If and when you have the time, a brief description of your edits would be
appreciated! So much so, I will personally drink a beer on your behalf. :) -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: I do not understand how you did that, If you are familiar with VBA, do you want an explanation? but it works great!!! I'm glad it worked out for you (I was a little worried that ND Pard's response might have kept you from looking back into this thread in order to see it). Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
Let's show you what I did with an example. Consider this string of text...
TextFromCell = "One two (Three Four) Five (Six) Seven" First off, since the Split function only works with a single delimiter, let convert the closing parentheses to opening parentheses, but in such a way that we can find them again later in order to turn them back to closing parentheses. To do that, I am going to replace all ')' with '()'.... ' TextLine is Dim'med as a simple String TextLine = Replace(TextFromCell, ")", "()") At this point, TextLine contains this... "One two (Three Four() Five (Six() Seven" Now, we split this using the open parenthesis as the delimiter. ' ParsedLine is Dim'med as a dynamic String array ParsedLine = Split(TextLine, "(") Okay, at this point the ParsedLine array has 5 elements (index numbers 0 through 4) Element 0: "One two " Element 1: "Three Four" Element 2: ") Five " Element 3: "Six" Element 4: ") Seven" Notice that text inside the parentheses are located at elements 1 and 3. As it turns out, no matter how many parentheses-grouped pieces of text you have, they will always occur at an odd-numbered element index... even if the text starts with an open parenthesis (with no text in front of it). So, to process only the text **not** located inside parentheses, all we have to do is loop through the even numbered element indexes starting with index number zero. The loop structure to do that is... For X = 0 To UBound(ParsedLine) Step 2 ' ' ParsedLine(X) is text not inside any parentheses, do something to it here ' Next Okay, now the elements of the array look like this (assuming we are upper casing it)... Element 0: "ONE TWO " Element 1: "Three Four" Element 2: ") FIVE " Element 3: "Six" Element 4: ") SEVEN" Now, we rejoin the array using the Join function and specify the opening parenthesis (what we used to break it the original text apart with) as the delimiter. Once this is done, our joined text string looks like this... TextLine = "ONE TWO (Three Four() FIVE (Six() SEVEN" All that is left is to replace the '()' symbol pair with ')" and assign it back to the cell where it came from... TextFromCell = Replace(TextLine, "()", ")") Now we do this for every cell in the range we are processing. Rick "Rick S." wrote in message ... If and when you have the time, a brief description of your edits would be appreciated! So much so, I will personally drink a beer on your behalf. :) -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: I do not understand how you did that, If you are familiar with VBA, do you want an explanation? but it works great!!! I'm glad it worked out for you (I was a little worried that ND Pard's response might have kept you from looking back into this thread in order to see it). Rick |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
This is great information and an excellent explenation as well. I truly
appreciate the time involved to explain this! -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: Let's show you what I did with an example. Consider this string of text... TextFromCell = "One two (Three Four) Five (Six) Seven" First off, since the Split function only works with a single delimiter, let convert the closing parentheses to opening parentheses, but in such a way that we can find them again later in order to turn them back to closing parentheses. To do that, I am going to replace all ')' with '()'.... ' TextLine is Dim'med as a simple String TextLine = Replace(TextFromCell, ")", "()") At this point, TextLine contains this... "One two (Three Four() Five (Six() Seven" Now, we split this using the open parenthesis as the delimiter. ' ParsedLine is Dim'med as a dynamic String array ParsedLine = Split(TextLine, "(") Okay, at this point the ParsedLine array has 5 elements (index numbers 0 through 4) Element 0: "One two " Element 1: "Three Four" Element 2: ") Five " Element 3: "Six" Element 4: ") Seven" Notice that text inside the parentheses are located at elements 1 and 3. As it turns out, no matter how many parentheses-grouped pieces of text you have, they will always occur at an odd-numbered element index... even if the text starts with an open parenthesis (with no text in front of it). So, to process only the text **not** located inside parentheses, all we have to do is loop through the even numbered element indexes starting with index number zero. The loop structure to do that is... For X = 0 To UBound(ParsedLine) Step 2 ' ' ParsedLine(X) is text not inside any parentheses, do something to it here ' Next Okay, now the elements of the array look like this (assuming we are upper casing it)... Element 0: "ONE TWO " Element 1: "Three Four" Element 2: ") FIVE " Element 3: "Six" Element 4: ") SEVEN" Now, we rejoin the array using the Join function and specify the opening parenthesis (what we used to break it the original text apart with) as the delimiter. Once this is done, our joined text string looks like this... TextLine = "ONE TWO (Three Four() FIVE (Six() SEVEN" All that is left is to replace the '()' symbol pair with ')" and assign it back to the cell where it came from... TextFromCell = Replace(TextLine, "()", ")") Now we do this for every cell in the range we are processing. Rick "Rick S." wrote in message ... If and when you have the time, a brief description of your edits would be appreciated! So much so, I will personally drink a beer on your behalf. :) -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: I do not understand how you did that, If you are familiar with VBA, do you want an explanation? but it works great!!! I'm glad it worked out for you (I was a little worried that ND Pard's response might have kept you from looking back into this thread in order to see it). Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help modify macro
This is great information and an excellent explenation as well. I
truly appreciate the time involved to explain this! My pleasure... I was glad to do it. Rick "Rick S." wrote in message ... This is great information and an excellent explenation as well. I truly appreciate the time involved to explain this! -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: Let's show you what I did with an example. Consider this string of text... TextFromCell = "One two (Three Four) Five (Six) Seven" First off, since the Split function only works with a single delimiter, let convert the closing parentheses to opening parentheses, but in such a way that we can find them again later in order to turn them back to closing parentheses. To do that, I am going to replace all ')' with '()'.... ' TextLine is Dim'med as a simple String TextLine = Replace(TextFromCell, ")", "()") At this point, TextLine contains this... "One two (Three Four() Five (Six() Seven" Now, we split this using the open parenthesis as the delimiter. ' ParsedLine is Dim'med as a dynamic String array ParsedLine = Split(TextLine, "(") Okay, at this point the ParsedLine array has 5 elements (index numbers 0 through 4) Element 0: "One two " Element 1: "Three Four" Element 2: ") Five " Element 3: "Six" Element 4: ") Seven" Notice that text inside the parentheses are located at elements 1 and 3. As it turns out, no matter how many parentheses-grouped pieces of text you have, they will always occur at an odd-numbered element index... even if the text starts with an open parenthesis (with no text in front of it). So, to process only the text **not** located inside parentheses, all we have to do is loop through the even numbered element indexes starting with index number zero. The loop structure to do that is... For X = 0 To UBound(ParsedLine) Step 2 ' ' ParsedLine(X) is text not inside any parentheses, do something to it here ' Next Okay, now the elements of the array look like this (assuming we are upper casing it)... Element 0: "ONE TWO " Element 1: "Three Four" Element 2: ") FIVE " Element 3: "Six" Element 4: ") SEVEN" Now, we rejoin the array using the Join function and specify the opening parenthesis (what we used to break it the original text apart with) as the delimiter. Once this is done, our joined text string looks like this... TextLine = "ONE TWO (Three Four() FIVE (Six() SEVEN" All that is left is to replace the '()' symbol pair with ')" and assign it back to the cell where it came from... TextFromCell = Replace(TextLine, "()", ")") Now we do this for every cell in the range we are processing. Rick "Rick S." wrote in message ... If and when you have the time, a brief description of your edits would be appreciated! So much so, I will personally drink a beer on your behalf. :) -- Regards VBA.Newb.Confused XP Pro Office 2007 "Rick Rothstein (MVP - VB)" wrote: I do not understand how you did that, If you are familiar with VBA, do you want an explanation? but it works great!!! I'm glad it worked out for you (I was a little worried that ND Pard's response might have kept you from looking back into this thread in order to see it). Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify A Macro | Excel Worksheet Functions | |||
Modify a Macro | Excel Worksheet Functions | |||
Modify Macro | Excel Programming | |||
Modify a macro | Excel Programming | |||
Help to modify macro please | Excel Programming |