Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment/Increment letter in alphabetical order
Does anybody know of some code which would "increase" the letter within a cell?
i.e. if the value of the cell was "A", when you select the cell and run the macro, it would change the cell value to "B". If you did it again it would change it to "C", etc... etc... I have found a way to do it using a helper cell, but I'd much prefer the macro to operate on just the one cell. My old method was as follows: A1 starts with the value 65. The formula in B1 = CHAR(A1). (So B1 starts as "A") I then selected A1 and used the following macro (assigned to a shortcut to make it quicker): Sub Add_1() Selection.Value = Selection.Value + 1 End Sub The reason for requesting this: Sometimes a cell needs to have a particular letter value to achieve the required output in a resultant cell, but I wouldn't know straight away what the letter needs to be. Rather than entering "A", checking the resultant cell, then entering "B", checking again, etc... (very slow and tedious), I could just keep my finger on the shortcut key until I see the resultant cell flicker, signalling that I had just passed the required letter value (much quicker). Can anybody think of a way (maybe using CODE or CHAR or something?) for this process to operate without the need for the number format helper cell in A1? It's really puzzling me, and would make life a lot easier. Thanks in advance, Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment/Increment letter in alphabetical order
Hi Neil,
Try: '============= Private Sub CommandButton1_Click() Dim rng As Range Set rng = Me.Range("A1") With rng .Value = Chr(Asc(.Value) + 1) End With End Sub '<<============= --- Regards, Norman "Neil Goldwasser" wrote in message ... Does anybody know of some code which would "increase" the letter within a cell? i.e. if the value of the cell was "A", when you select the cell and run the macro, it would change the cell value to "B". If you did it again it would change it to "C", etc... etc... I have found a way to do it using a helper cell, but I'd much prefer the macro to operate on just the one cell. My old method was as follows: A1 starts with the value 65. The formula in B1 = CHAR(A1). (So B1 starts as "A") I then selected A1 and used the following macro (assigned to a shortcut to make it quicker): Sub Add_1() Selection.Value = Selection.Value + 1 End Sub The reason for requesting this: Sometimes a cell needs to have a particular letter value to achieve the required output in a resultant cell, but I wouldn't know straight away what the letter needs to be. Rather than entering "A", checking the resultant cell, then entering "B", checking again, etc... (very slow and tedious), I could just keep my finger on the shortcut key until I see the resultant cell flicker, signalling that I had just passed the required letter value (much quicker). Can anybody think of a way (maybe using CODE or CHAR or something?) for this process to operate without the need for the number format helper cell in A1? It's really puzzling me, and would make life a lot easier. Thanks in advance, Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment/Increment letter in alphabetical order
Hi Neil,
Or, perhaps: '============= Private Sub CommandButton1_Click() Dim rng As Range Set rng = Me.Range("A1") With rng If .Value = "Z" Then .Value = "A" Else .Value = Chr(Asc(.Value) + 1) End If End With End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Neil, Try: '============= Private Sub CommandButton1_Click() Dim rng As Range Set rng = Me.Range("A1") With rng .Value = Chr(Asc(.Value) + 1) End With End Sub '<<============= --- Regards, Norman "Neil Goldwasser" wrote in message ... Does anybody know of some code which would "increase" the letter within a cell? i.e. if the value of the cell was "A", when you select the cell and run the macro, it would change the cell value to "B". If you did it again it would change it to "C", etc... etc... I have found a way to do it using a helper cell, but I'd much prefer the macro to operate on just the one cell. My old method was as follows: A1 starts with the value 65. The formula in B1 = CHAR(A1). (So B1 starts as "A") I then selected A1 and used the following macro (assigned to a shortcut to make it quicker): Sub Add_1() Selection.Value = Selection.Value + 1 End Sub The reason for requesting this: Sometimes a cell needs to have a particular letter value to achieve the required output in a resultant cell, but I wouldn't know straight away what the letter needs to be. Rather than entering "A", checking the resultant cell, then entering "B", checking again, etc... (very slow and tedious), I could just keep my finger on the shortcut key until I see the resultant cell flicker, signalling that I had just passed the required letter value (much quicker). Can anybody think of a way (maybe using CODE or CHAR or something?) for this process to operate without the need for the number format helper cell in A1? It's really puzzling me, and would make life a lot easier. Thanks in advance, Neil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment/Increment letter in alphabetical order
You're a star! Absolutely spot on!
Thank you very much - by looking at your code I feel that I am starting to pick up quite a lot of stuff now! Cheers again, Neil "Norman Jones" wrote: Hi Neil, Or, perhaps: '============= Private Sub CommandButton1_Click() Dim rng As Range Set rng = Me.Range("A1") With rng If .Value = "Z" Then .Value = "A" Else .Value = Chr(Asc(.Value) + 1) End If End With End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Neil, Try: '============= Private Sub CommandButton1_Click() Dim rng As Range Set rng = Me.Range("A1") With rng .Value = Chr(Asc(.Value) + 1) End With End Sub '<<============= --- Regards, Norman "Neil Goldwasser" wrote in message ... Does anybody know of some code which would "increase" the letter within a cell? i.e. if the value of the cell was "A", when you select the cell and run the macro, it would change the cell value to "B". If you did it again it would change it to "C", etc... etc... I have found a way to do it using a helper cell, but I'd much prefer the macro to operate on just the one cell. My old method was as follows: A1 starts with the value 65. The formula in B1 = CHAR(A1). (So B1 starts as "A") I then selected A1 and used the following macro (assigned to a shortcut to make it quicker): Sub Add_1() Selection.Value = Selection.Value + 1 End Sub The reason for requesting this: Sometimes a cell needs to have a particular letter value to achieve the required output in a resultant cell, but I wouldn't know straight away what the letter needs to be. Rather than entering "A", checking the resultant cell, then entering "B", checking again, etc... (very slow and tedious), I could just keep my finger on the shortcut key until I see the resultant cell flicker, signalling that I had just passed the required letter value (much quicker). Can anybody think of a way (maybe using CODE or CHAR or something?) for this process to operate without the need for the number format helper cell in A1? It's really puzzling me, and would make life a lot easier. Thanks in advance, Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to Increment by Letter not Number | Excel Worksheet Functions | |||
Formula copy paste down in a sheet but change row letter increment | Excel Discussion (Misc queries) | |||
Increment | Excel Worksheet Functions | |||
Increment Macro - Order numbers | Excel Programming | |||
need to increment value | Excel Discussion (Misc queries) |