| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A little VBA help please: collect row value not column
I need to change this code so it collects the information from the row and
place it in the appropriate row. As it is written now it is doing the opposite - collecting the information from the column down and placing it in another column down. Specifically it needs to: Collect infor 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. Here's my current code: Sub EnterYearlyAttendanceRecord() Select Case LCase(Range("'Lunch and Attendance'!AF4")) Case "august" 'type month in lower case Case "september" Case "october" Case Else End Select 'August Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7, "ad").Resize(31).Value End Sub -- Mike Mast Special Education Preschool Teacher |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A little VBA help please: collect row value not column
Try the below
Sheets("Attendance1").Cells(30, "h").Resize(1, 31).Value = _ WorksheetFunction.Transpose(Sheets("Lunch and Attendance").Cells(7, _ "ad").Resize(31)) If this post helps click Yes --------------- Jacob Skaria "Preschool Mike" wrote: I need to change this code so it collects the information from the row and place it in the appropriate row. As it is written now it is doing the opposite - collecting the information from the column down and placing it in another column down. Specifically it needs to: Collect infor 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. Here's my current code: Sub EnterYearlyAttendanceRecord() Select Case LCase(Range("'Lunch and Attendance'!AF4")) Case "august" 'type month in lower case Case "september" Case "october" Case Else End Select 'August Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7, "ad").Resize(31).Value End Sub -- Mike Mast Special Education Preschool Teacher |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A little VBA help please: collect row value not column
Mike,
From the looks of it, you want to store August in row 7, September in row 8, etc. If so, then try: Sub EnterYearlyAttendanceRecord2() Dim monOffset As Integer monOffset = Month(DateValue(Range("'Lunch and Attendance'!AF4") & " 1, " & Year(Now()))) If monOffset 7 Then monOffset = monOffset - 8 Else monOffset = monOffset + 4 End If Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7 + monOffset, "ad").Resize(31).Value End Sub HTH, Bernie MS Excel MVP "Preschool Mike" wrote in message ... I need to change this code so it collects the information from the row and place it in the appropriate row. As it is written now it is doing the opposite - collecting the information from the column down and placing it in another column down. Specifically it needs to: Collect infor 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. Here's my current code: Sub EnterYearlyAttendanceRecord() Select Case LCase(Range("'Lunch and Attendance'!AF4")) Case "august" 'type month in lower case Case "september" Case "october" Case Else End Select 'August Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7, "ad").Resize(31).Value End Sub -- Mike Mast Special Education Preschool Teacher |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A little VBA help please: collect row value not column
Oops - I missed what Don saw - so make sure you change each 31 to ,31
HTH, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Mike, From the looks of it, you want to store August in row 7, September in row 8, etc. If so, then try: Sub EnterYearlyAttendanceRecord2() Dim monOffset As Integer monOffset = Month(DateValue(Range("'Lunch and Attendance'!AF4") & " 1, " & Year(Now()))) If monOffset 7 Then monOffset = monOffset - 8 Else monOffset = monOffset + 4 End If Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7 + monOffset, "ad").Resize(31).Value End Sub HTH, Bernie MS Excel MVP "Preschool Mike" wrote in message ... I need to change this code so it collects the information from the row and place it in the appropriate row. As it is written now it is doing the opposite - collecting the information from the column down and placing it in another column down. Specifically it needs to: Collect infor 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. Here's my current code: Sub EnterYearlyAttendanceRecord() Select Case LCase(Range("'Lunch and Attendance'!AF4")) Case "august" 'type month in lower case Case "september" Case "october" Case Else End Select 'August Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7, "ad").Resize(31).Value End Sub -- Mike Mast Special Education Preschool Teacher |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A little VBA help please: collect row value not column
Sorry but this code did not work, but Don's suggestion did. Thanks for your
time. -- Mike Mast Special Education Preschool Teacher "Jacob Skaria" wrote: Try the below Sheets("Attendance1").Cells(30, "h").Resize(1, 31).Value = _ WorksheetFunction.Transpose(Sheets("Lunch and Attendance").Cells(7, _ "ad").Resize(31)) If this post helps click Yes --------------- Jacob Skaria "Preschool Mike" wrote: I need to change this code so it collects the information from the row and place it in the appropriate row. As it is written now it is doing the opposite - collecting the information from the column down and placing it in another column down. Specifically it needs to: Collect infor 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. Here's my current code: Sub EnterYearlyAttendanceRecord() Select Case LCase(Range("'Lunch and Attendance'!AF4")) Case "august" 'type month in lower case Case "september" Case "october" Case Else End Select 'August Sheets("Attendance1").Cells(30, "h").Resize(31).Value = _ Sheets("Lunch and Attendance").Cells(7, "ad").Resize(31).Value End Sub -- Mike Mast Special Education Preschool Teacher |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I collect excell spreadsheets from 30 people? | Excel Discussion (Misc queries) | |||
| use a worksheet as a form to collect data | Excel Worksheet Functions | |||
| How to collect data from every 60th row? | Excel Discussion (Misc queries) | |||
| Collect numbers.... | Excel Discussion (Misc queries) | |||
| collect data from different worksheet | Excel Worksheet Functions | |||
Linear Mode
