Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11,272
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum a Range Dependent on Non-zero Entries RichUE Excel Discussion (Misc queries) 2 February 18th 10 11:16 AM
SUM a range dependent on dates Qaspec Excel Discussion (Misc queries) 4 July 6th 09 02:03 PM
Selecting range in list of range names depending on a cell informa Courreges Excel Discussion (Misc queries) 2 June 19th 06 10:59 AM
How do I count data in range A:A that is dependent upon criteria . h2ocats Excel Worksheet Functions 1 February 21st 05 01:55 PM
Selecting a Range inside a range hcova Excel Programming 0 July 13th 04 03:26 PM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"