Charles Chickering
12/13/2006 8:24:00 PM
Oops, my offset is screwed up, I told it to offset rows instead of columns.
This is also the cause of the second to last entry screw up, if you noticed,
I did not re-update the range rCopyTo, so the data for the second to last
agent is being over written by the last agent. Try this:
Sub CopyFirstLast()
Dim rAgent As Range
Dim rLastAgent As Range
Dim rCopyTo As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set rAgent = ws1.Range("A1")
Set rLastAgent = ws1.Range("A" & ws1.Rows.Count).End(xlUp)
Do
Set rCopyTo = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1)
rAgent.Copy rCopyTo
rAgent.Offset(1,1).Copy rCopyTo.Offset(,1)
Set rAgent = rAgent.End(xlDown)
rAgent.Offset(-1,1).Copy rCopyTo.Offset(,2)
Loop Until rAgent.Address = rLastAgent.Address
rAgent.Copy rCopyTo.Offset(1)
rAgent.Offset(1,1).Copy rCopyTo.Offset(1,1)
rAgent.Offset(1,1).End(xlDown).Copy rCopyTo.Offset(1,2)
End Sub
--
Charles Chickering
"A good example is twice the value of good advice."
"Mir Khan" wrote:
> i appreciate your help, please see below for the output it is generating. i
> need to have top values in one column and bottom values in another.
>
> this code is also messing up with the second last range...
>
>
> Agent1
> 06:48:14
> 18:30:46
> Agent2
> 07:49:24
> 16:31:32
> Agent3
> 06:59:05
> 15:31:59
> Agent4
> 06:48:22
> 15:31:23
> Agent5
> 06:56:47
> 15:30:48
> Agent6
> Agent7 08:26:44 17:00:01
> 17:59:39
>
>
> thanks
>
> mir khan
>
> "Charles Chickering" wrote:
>
> > One way....
> > Sub CopyFirstLast()
> > Dim rAgent As Range
> > Dim rLastAgent As Range
> > Dim rCopyTo As Range
> > Dim ws1 As Worksheet
> > Dim ws2 As Worksheet
> > Set ws1 = Worksheets("Sheet1")
> > Set ws2 = Worksheets("Sheet2")
> > Set rAgent = ws1.Range("A1")
> > Set rLastAgent = ws1.Range("A" & ws1.Rows.Count).End(xlUp)
> > Do
> > Set rCopyTo = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(1)
> > rAgent.Copy rCopyTo
> > rAgent.Offset(1,1).Copy rCopyTo.Offset(1)
> > Set rAgent = rAgent.End(xlDown)
> > rAgent.Offset(-1,1).Copy rCopyTo.Offset(2)
> > Loop Until rAgent.Address = rLastAgent.Address
> > rAgent.Copy rCopyTo.Offset(1)
> > rAgent.Offset(1,1).Copy rCopyTo.Offset(1,1)
> > rAgent.Offset(1,1).End(xlDown).Copy rCopyTo.Offset(1,2)
> > End Sub
> >
> >
> > --
> > Charles Chickering
> >
> > "A good example is twice the value of good advice."
> >
> >
> > "Mir Khan" wrote:
> >
> > > Hi Experts, i need your help, i have a 2 columns data column 1 has name of
> > > the agent and column 2 has agent's login logout timings for the day. i need
> > > to copy top and bottom values of the all the agents. all the top values in
> > > one column and bottom values in another column on sheet 2.
> > >
> > > please read to know how the data is set up in the sheet....
> > > name of agent one appears in R1C1 and login logout time entry starts from
> > > R2C2 and runs through till the end. and R2C1 till the next agent name is
> > > blank.
> > >
> > > sample data
> > >
> > > Agent1
> > > 06:48:14
> > > 06:48:14
> > > 09:00:21
> > > 09:15:37
> > > 09:15:37
> > > 11:30:08
> > > Agent2
> > > 11:59:05
> > > 11:59:05
> > > 14:15:02
> > > 14:30:25
> > > Agent3
> > > 14:30:25
> > > 18:30:46
> > > 20:55:56
> > >
> > >
> > > i please need answer to this, please tell me if i need to send file to
> > > someone who can help me...
> > >
> > > please please help me.
> > >
> > >
> > >
> > >