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

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

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

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