Select Case ignored

[Preschool Mike]

What have I done wrong? My Select Case is being ignored in the below code.
It does not even look for what is type in cell AF2 on sheet Lunch and
Attendance. I can leave AF2 blank and it collect the information and places
it in the same row everytime.

Specifically it needs to: Collect information from Sheets(Lunch and
Attendance,
cells AD7 thru BH7) and place it in Sheets (Attendance1, cells H30 thru AL
30) if the current month in Sheets (Lunch and Attendance, cell AF4) is
August. If the month would be September then it will do the same except
collect and store the information in the rows below August and so fourth.



Sub EnterYearlyAttendanceRecord()
Select Case LCase(Range("'Lunch and Attendance'!AF2"))
Case "august" 'type month in lower case
Case "september"
Case "october"
Case Else
End Select

Sheets("Attendance1").Cells(30, "h").Resize(, 31).Value = _
Sheets("Lunch and Attendance").Cells(7, "ad").Resize(, 31).Value
Sheets("Attendance2").Cells(30, "h").Resize(, 31).Value = _
Sheets("Lunch and Attendance").Cells(8, "ad").Resize(, 31).Value
End Sub

 

[Luke M]

You need to tell it what to do for each case. Structure is:

Select Case LCase(Range("'Lunch and Attendance'!AF2"))
Case "august"
'Do stuff for august here
Case "september"
'Do something different here
'....etc
End Select

 

[Bernie Deitrick]

You are not properly addressing the cell in VBA style - you are using formula style referencing.

(Range("'Lunch and Attendance'!AF2"))

should be

Worksheets("'Lunch and Attendance").Range("AF2").Value

 

[Bernie Deitrick]

Sorry - your string reference is properly formed, so ignore this...

Bernie
MS Excel MVP

You are not properly addressing the cell in VBA style - you are using formula style referencing.

(Range("'Lunch and Attendance'!AF2"))

should be

Worksheets("'Lunch and Attendance").Range("AF2").Value

 

[Dave Peterson]

Actually, this will depend on where the code is located.

If this (non-standard) syntax
Select Case LCase(Range("'Lunch and Attendance'!AF2"))
is used in a worksheet module (not behind "lunch and attendance"), then you'll
see an error.

I can't think of a time where I'd use this syntax--even though it would work
some places.

I'd always use what you suggested:
Select Case LCase(Worksheets("Lunch and Attendance").Range("AF2").Value)
(with a correction for a typo--don't include that apostrophe <vbg>.)

Sorry - your string reference is properly formed, so ignore this...

Bernie
MS Excel MVP