Using Case Statement

[50gumbys]

I need to check a cell (eg A1) in Excel for:
- the day of the month (date is entered as dd/mm/yy)
- if the day is between 1 - 31, I want to go to the relevant cell (eg - B1 - B31) in order to enter today's entry.

I'm going around in circles with the Case statement!
(hey - it IS a Case statement, isn't it? :dunno )

Can anyone help please ???
Many thanks to you SO much!!!

Lori

[XLGibbs]

How about


Sub ttt()
Dim r as Long

r = Day(Sheets("Sheet1").Range("A1"))

Range("B" & r) = "Works"
End Sub

[Bob Phillips]

You could use a Case statement and test every possible value, a bit of overkill I would suggest.

This does what you ask



With Worksheets("Sheet1")
If IsNumeric(.Range("A1").Value) Then
If .Range("A1").Value > 0 And .Range("A1").Value < 32 Then
Application.Goto .Range("B" & .Range("A1").Value)
Else
MsgBox "Invalid value"
End If
Else
MsgBox "Not a number"
End If
End With

[mdmackillop]

Hi Lori,
Tweaking Bob's code 'cos A1 is a date, try
Sub DayRow()
With Worksheets("Sheet1")
If IsDate(.Range("A1").Value) Then
Application.Goto .Range("B" & Day(.Range("A1").Value))
Else
MsgBox "Invalid value"
End If
End With
End Sub

[CBrine]

And one other thing.

If you want this action to occur automatically, you will need to add it to the worksheet change event.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target = ActiveSheet.Range("A1") And ActiveSheet.Range("A1") <> "" Then
With Worksheets("Sheet1")
If IsDate(.Range("A1").Value) Then
Application.Goto .Range("B" & Day(.Range("A1").Value))
Else
MsgBox "Invalid value"
End If
End With
End If
End Sub



Just add this to the sheet level, in the VBA editor, for the sheet you want the code to act on.

HTH
Cal