I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Jim, that is close to what I need. I'm sorry for the thin description. I have
a sheet that is used to schedule employees. Col A is for names, B is for
start time, C is for end time and D is for hours worked. When someone is
scheduled for vacation, sick time, or a holiday I want to be able to enter
the appropriate letter in column B and have it return 8 (as in hours) for
that employee.

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

Remember, you're still changing the cell to the right

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(Target, Me.Range("a1:a9,C3:c99,e14:e199,x5")) Is Nothing Then
Exit Sub
End If

Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select

End Sub


MikeG wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson


"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

Sure if you want the code to work you can do that but I thought that this was
much more inventive... ;-) Thanks for catching that one...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

I think that this:
If not intersect (Target.Column, Range("B2:B100") is nothing Then
should be:
If not intersect (Target, Range("B2:B100")) is nothing Then

Target.column will return a number (and a typo missing that final close
parenthesis).

Jim Thomlinson wrote:

here it is for changed made to B2 through B100, updating column D... This
should be a bit closer...

Private Sub Worksheet_Change(ByVal Target As Range)
If not intersect (Target.Column, Range("B2:B100") is nothing Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 2).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub

--
HTH...

Jim Thomlinson

"MikeG" wrote:

Can this be edited so that the Target is a range of cells rather than a column?

"Jim Thomlinson" wrote:

Your description is a little thin. Here is some event code that responds to
changes in column A and places a value beside the cell that was changed.
Right click the sheet tab and select view code. Paste the following...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case UCase(Target.Value)
Case "A", "H", "V"
Target.Offset(0, 1).Value = 8
Case Else
MsgBox "Not A, H or V."
End Select
End If
End Sub
--
HTH...

Jim Thomlinson


"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?

--

Dave Peterson

Are you required to use VBA?
Will cell validation work instead?

See http://www.contextures.com/xlDataVal01.html

Thx
MSweetG222



"MikeG" wrote:

I would like to be able to enter the letters "V", "H" or "A" into a cell and
have the number 8 entered into another cell. How can this be done using
Select Case?