Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help please in selecting range dependent on another range
Hi,
I have a workbook with a worksheet named "master", the data is similar to this: Name Surname Mon am Mon pm Tue am Tue pm a a Y Y Y b b Y Y Y c c Y Y Y d d Y Y etc I have to sort the data for each day and by surname and copy the "name" and "surname" data into the worksheet of the corresponding "day" (this I can do). I am not sure how to select the data in columns "Name" and "Surname" down to the point where in the column of the chosen "(day name)" the last Y value is present. Taking the above example, if I sorted on "Mon am" then the data I would have to select and copy is names and surname "a", "c", and "d". If I sorted on "Tue am" then the name and surname would be "b" and "c". Can anyone help me with the code needed to select the correct data? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help please in selecting range dependent on another range
Mick,
Here is some code that does it all Sub move() Dim cLastRow As Long, cLastCol As Long Dim cLast As Long Dim i As Long, j As Long With ActiveSheet cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row cLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column For j = 3 To cLastCol Worksheets(.Cells(1, j).Value).Cells(1, "A") = .Cells(1, "A").Value Worksheets(.Cells(1, j).Value).Cells(1, "B") = .Cells(1, "B").Value Next j For i = 2 To cLastRow For j = 3 To cLastCol If .Cells(i, j).Value = "Y" Then cLast = Worksheets(.Cells(1, j).Value).Cells(Rows.Count, "A").End(xlUp).Row cLast = cLast + 1 Worksheets(.Cells(1, j).Value).Cells(cLast, "A").Value = _ .Cells(i, "A").Value Worksheets(.Cells(1, j).Value).Cells(cLast, "B").Value = _ .Cells(i, "B").Value End If Next j Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "MickJJ" wrote in message ... Hi, I have a workbook with a worksheet named "master", the data is similar to this: Name Surname Mon am Mon pm Tue am Tue pm a a Y Y Y b b Y Y Y c c Y Y Y d d Y Y etc I have to sort the data for each day and by surname and copy the "name" and "surname" data into the worksheet of the corresponding "day" (this I can do). I am not sure how to select the data in columns "Name" and "Surname" down to the point where in the column of the chosen "(day name)" the last Y value is present. Taking the above example, if I sorted on "Mon am" then the data I would have to select and copy is names and surname "a", "c", and "d". If I sorted on "Tue am" then the name and surname would be "b" and "c". Can anyone help me with the code needed to select the correct data? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help please in selecting range dependent on another range
Hi Bob
Thanks for this, it's just what I needed and will save me a great deal of work. All I have to do now is try and understand whats going on with it...lol. I'm sure I'll be able to unravel the mystery and most importantly learn from it. I owe you 1. Regards Mick "Bob Phillips" wrote: Mick, Here is some code that does it all Sub move() Dim cLastRow As Long, cLastCol As Long Dim cLast As Long Dim i As Long, j As Long With ActiveSheet cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row cLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column For j = 3 To cLastCol Worksheets(.Cells(1, j).Value).Cells(1, "A") = .Cells(1, "A").Value Worksheets(.Cells(1, j).Value).Cells(1, "B") = .Cells(1, "B").Value Next j For i = 2 To cLastRow For j = 3 To cLastCol If .Cells(i, j).Value = "Y" Then cLast = Worksheets(.Cells(1, j).Value).Cells(Rows.Count, "A").End(xlUp).Row cLast = cLast + 1 Worksheets(.Cells(1, j).Value).Cells(cLast, "A").Value = _ .Cells(i, "A").Value Worksheets(.Cells(1, j).Value).Cells(cLast, "B").Value = _ .Cells(i, "B").Value End If Next j Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "MickJJ" wrote in message ... Hi, I have a workbook with a worksheet named "master", the data is similar to this: Name Surname Mon am Mon pm Tue am Tue pm a a Y Y Y b b Y Y Y c c Y Y Y d d Y Y etc I have to sort the data for each day and by surname and copy the "name" and "surname" data into the worksheet of the corresponding "day" (this I can do). I am not sure how to select the data in columns "Name" and "Surname" down to the point where in the column of the chosen "(day name)" the last Y value is present. Taking the above example, if I sorted on "Mon am" then the data I would have to select and copy is names and surname "a", "c", and "d". If I sorted on "Tue am" then the name and surname would be "b" and "c". Can anyone help me with the code needed to select the correct data? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum a Range Dependent on Non-zero Entries | Excel Discussion (Misc queries) | |||
SUM a range dependent on dates | Excel Discussion (Misc queries) | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
Selecting a Range inside a range | Excel Programming |