Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Case ignored
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 -- Mike Mast Special Education Preschool Teacher |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Case ignored
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Preschool Mike" wrote: 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 -- Mike Mast Special Education Preschool Teacher |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Case ignored
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 -- HTH, Bernie MS Excel MVP "Preschool Mike" wrote in message ... 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 -- Mike Mast Special Education Preschool Teacher |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Case ignored
Sorry - your string reference is properly formed, so ignore this...
Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 -- HTH, Bernie MS Excel MVP "Preschool Mike" wrote in message ... 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 -- Mike Mast Special Education Preschool Teacher |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Select Case ignored
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.) Bernie Deitrick wrote: Sorry - your string reference is properly formed, so ignore this... Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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 -- HTH, Bernie MS Excel MVP "Preschool Mike" wrote in message ... 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 -- Mike Mast Special Education Preschool Teacher -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Case | Excel Discussion (Misc queries) | |||
Case Select | Excel Worksheet Functions | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Select Case | Excel Discussion (Misc queries) | |||
Need help on Select Case | Excel Worksheet Functions |