Looking at a cell A1 and changing value in B1 with macro!

[eddy]

Sounds easy but this is I would like.
Cell A1 could contain nothing, a,b,c, or d (alpha characters)
I would like the following to be true:-
If cell A1 = nothing then cell B1 = a
If cell A1 = a then cell B1 = b
If cell A1 = b then cell B1 = c
If cell A1 = c then cell B1 = d
If cell A1 = d then cell B1 = e
Thanks ed

_________________
From eD
traspberries@hotmail.com
This message was edited by eddy on 2002-03-05 14:08
This message was edited by eddy on 2002-03-05 14:09
This message was edited by eddy on 2002-03-05 14:16

 

[Tom Urtis]

See if this works for you. Right click on your sheet tab, left click on View Code, and paste this in:

''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$A$1" Or Target.Cells.Count > 1 Then Exit Sub

Select Case [A1].Value

Case ""
[B1] = "a"
Case "a"
[B1] = "b"
Case "b"
[B1] = "c"
Case "c"
[B1] = "d"
Case "d"
[B1] = "e"
Case Else
Exit Sub

End Select

End Sub

'''''''''''''''''''''''''''''''''''

Tom Urtis

 

[Guest]

Dim intCell, test
With ActiveSheet
intCell = 1
Do Until intCell = 7
If .Cells(intCell, 1) = "" Then
.Cells(intCell, 2) = "a"
Else
.Cells(intCell, 2) = Chr(Asc(.Cells(intCell, 1)) + 1)
End If
intCell = intCell + 1
Loop
End With
End Sub



Or a formula in B1:B6
=IF(A1="","a",CHAR(CODE(A1)+1))

 

[eddy]

GREAT !!!
Thanks everyone they all worked fine. In the end I was able to use the very simple formula =IF(A1=" ","a",CHAR(CODE(A1)+1)). It works a treat.
To you all many,many thanks for your trouble!
Rgds eD