Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can this Micro customized. Please help
Sub Names()
For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(cll.Value) Next cll End Sub This is an old micro which works perfectly. I need help to make two changes into it. 1. Column A and B are text column for names, instead of capital letters I want those column in sentence case i.e. ANDREW into Andrew. (Everything else should stay the same, comas to make it four if characters are less than four) 2. Column C which is also a text column for names, I wan it to be capitalized once I press the €œName Correction€ micro. Any help to customize the above micro for this purpose will be greatly appreciated. I thank you for considerations in regard. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can this Micro customized. Please help
Hi
Try Option Explicit Sub Names() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" _ Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255) Next cll End Sub Sub NameCorrection() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("C:C")) cll.Value = UCase(cll.Value) Next cll End Sub -- Regards Roger Govier Help on formula wrote: Sub Names() For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(cll.Value) Next cll End Sub This is an old micro which works perfectly. I need help to make two changes into it. 1. Column A and B are text column for names, instead of capital letters I want those column in sentence case i.e. ANDREW into Andrew. (Everything else should stay the same, comas to make it four if characters are less than four) 2. Column C which is also a text column for names, I wan it to be capitalized once I press the €œName Correction€ micro. Any help to customize the above micro for this purpose will be greatly appreciated. I thank you for considerations in regard. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can this Micro customized. Please help
Thank you very much Roger. I deleted
End Sub Sub NameCorrection() Dim cll As Range and it work great (since I removed these lines, I dont know if technically something else need to add or remove from the code. Name Correction is just a name of micro, I right click and edit changes into it. The only problem I have now is the list of names which already stored in upper case (in column A,B and C) and formula not changing it unless I re-enter in lower case. I realize I should mention this in my question. Thanks for helping Hi Try Option Explicit Sub Names() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" _ Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255) Next cll End Sub Sub NameCorrection() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("C:C")) cll.Value = UCase(cll.Value) Next cll End Sub -- Regards Roger Govier Help on formula wrote: Sub Names() For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(cll.Value) Next cll End Sub This is an old micro which works perfectly. I need help to make two changes into it. 1. Column A and B are text column for names, instead of capital letters I want those column in sentence case i.e. ANDREW into Andrew. (Everything else should stay the same, comas to make it four if characters are less than four) 2. Column C which is also a text column for names, I wan it to be capitalized once I press the €œName Correction€ micro. Any help to customize the above micro for this purpose will be greatly appreciated. I thank you for considerations in regard. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can this Micro customized. Please help
Hi
Then change the line cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255) to cll.Value = UCase(Left(cll.Value, 1)) & Lcase(Mid(cll.Value, 2, 255)) -- Regards Roger Govier Help on formula wrote: Thank you very much Roger. I deleted End Sub Sub NameCorrection() Dim cll As Range and it work great (since I removed these lines, I dont know if technically something else need to add or remove from the code. Name Correction is just a name of micro, I right click and edit changes into it. The only problem I have now is the list of names which already stored in upper case (in column A,B and C) and formula not changing it unless I re-enter in lower case. I realize I should mention this in my question. Thanks for helping Hi Try Option Explicit Sub Names() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" _ Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255) Next cll End Sub Sub NameCorrection() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("C:C")) cll.Value = UCase(cll.Value) Next cll End Sub -- Regards Roger Govier Help on formula wrote: Sub Names() For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(cll.Value) Next cll End Sub This is an old micro which works perfectly. I need help to make two changes into it. 1. Column A and B are text column for names, instead of capital letters I want those column in sentence case i.e. ANDREW into Andrew. (Everything else should stay the same, comas to make it four if characters are less than four) 2. Column C which is also a text column for names, I wan it to be capitalized once I press the €œName Correction€ micro. Any help to customize the above micro for this purpose will be greatly appreciated. I thank you for considerations in regard. . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can this Micro customized. Please help
Thank you very much. It works perfect now. I salute your knowledge and
courtesy. Thanks again "Roger Govier" wrote: Hi Then change the line cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255) to cll.Value = UCase(Left(cll.Value, 1)) & Lcase(Mid(cll.Value, 2, 255)) -- Regards Roger Govier Help on formula wrote: Thank you very much Roger. I deleted End Sub Sub NameCorrection() Dim cll As Range and it work great (since I removed these lines, I don€„¢t know if technically something else need to add or remove from the code. Name Correction is just a name of micro, I right click and edit changes into it. The only problem I have now is the list of names which already stored in upper case (in column A,B and C) and formula not changing it unless I re-enter in lower case. I realize I should mention this in my question. Thanks for helping Hi Try Option Explicit Sub Names() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" _ Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(Left(cll.Value, 1)) & Mid(cll.Value, 2, 255) Next cll End Sub Sub NameCorrection() Dim cll As Range For Each cll In Intersect(ActiveSheet.UsedRange, Columns("C:C")) cll.Value = UCase(cll.Value) Next cll End Sub -- Regards Roger Govier Help on formula wrote: Sub Names() For Each cll In Intersect(ActiveSheet.UsedRange, Columns("A:B")) If Len(cll.Value) < 4 And cll.Value < "" Then cll.Value = Left(cll.Value & ",,,,", 4) cll.Value = UCase(cll.Value) Next cll End Sub This is an old micro which works perfectly. I need help to make two changes into it. 1. Column A and B are text column for names, instead of capital letters I want those column in sentence case i.e. ANDREW into Andrew. (Everything else should stay the same, comas to make it four if characters are less than four) 2. Column C which is also a text column for names, I wan it to be capitalized once I press the €ŀœName Correction€ micro. Any help to customize the above micro for this purpose will be greatly appreciated. I thank you for considerations in regard. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Micro | Excel Worksheet Functions | |||
Micro-Soft Pop-Ups | Excel Discussion (Misc queries) | |||
How do i run a micro from a formula | Excel Discussion (Misc queries) | |||
Micro | Excel Worksheet Functions | |||
what does the micro do | New Users to Excel |