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

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

A little VBA help please: collect row value not column
 

A cursory look suggests that all you need to do is put a , in front of the
31
Sheets("Attendance1").Cells(30, "h").Resize(,31).Value = _
Sheets("Lunch and Attendance").Cells(7, "ad").Resize(,31).Value



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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

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