Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Sorting by two column and bringing third along.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default Sorting by two column and bringing third along.
I am using a worksheet to enter data, this code copies and resorts by the
second column and populated a second worksheet. and works great (thank you
to the kind soul who gave this to me BTW)
I'm trying to modify it to bring a third column along for the ride. I don' t
need to sort by the third column, just copy it into the second worksheet
into the same column it belongs in.

I added all references to "C" in the code below but I can't figure out how
to simply modify this to include the third column. It looks to me like the
range and DSTCol are beyond my abilities at present.
I "Watched" the Vars for Range in the debugger but got more of a property
sheet than a range of cells. So I got lost.


Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol + 1))
End With

End Sub

Help?


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Sorting by two column and bringing third along.
This code is pretty verbose, but since you are happy with it:

Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
' change 1 to 2
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 2))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
' change 1 to 2
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 2))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range("A2")
End With

End Sub

--
Regards,
Tom Ogilvy


" wrote:

I am using a worksheet to enter data, this code copies and resorts by the
second column and populated a second worksheet. and works great (thank you
to the kind soul who gave this to me BTW)
I'm trying to modify it to bring a third column along for the ride. I don' t
need to sort by the third column, just copy it into the second worksheet
into the same column it belongs in.

I added all references to "C" in the code below but I can't figure out how
to simply modify this to include the third column. It looks to me like the
range and DSTCol are beyond my abilities at present.
I "Watched" the Vars for Range in the debugger but got more of a property
sheet than a range of cells. So I got lost.


Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol + 1))
End With

End Sub

Help?



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default Sorting by two column and bringing third along.
Thank you, I'll try that first thing tomorrow.
What does " End(xlUp) " do here?
I would welcome a less cumbersome alternative, maybe I can follow it's flow
and understand it instead of just pasting it in.

"Tom Ogilvy" wrote in message
...
This code is pretty verbose, but since you are happy with it:

Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
' change 1 to 2
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 2))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
' change 1 to 2
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 2))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range("A2")
End With

End Sub

--
Regards,
Tom Ogilvy


" wrote:

I am using a worksheet to enter data, this code copies and resorts by the
second column and populated a second worksheet. and works great (thank
you
to the kind soul who gave this to me BTW)
I'm trying to modify it to bring a third column along for the ride. I
don' t
need to sort by the third column, just copy it into the second worksheet
into the same column it belongs in.

I added all references to "C" in the code below but I can't figure out
how
to simply modify this to include the third column. It looks to me like
the
range and DSTCol are beyond my abilities at present.
I "Watched" the Vars for Range in the debugger but got more of a
property
sheet than a range of cells. So I got lost.


Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol +
1))
End With

End Sub

Help?





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Sorting by two column and bringing third along.
go to row 65536 and select A65536. Hit the End Key, then the Up Arrow.

That is what is means.

--
Regards,
Tom Ogilvy


" wrote:

Thank you, I'll try that first thing tomorrow.
What does " End(xlUp) " do here?
I would welcome a less cumbersome alternative, maybe I can follow it's flow
and understand it instead of just pasting it in.

"Tom Ogilvy" wrote in message
...
This code is pretty verbose, but since you are happy with it:

Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
' change 1 to 2
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 2))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
' change 1 to 2
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 2))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range("A2")
End With

End Sub

--
Regards,
Tom Ogilvy


" wrote:

I am using a worksheet to enter data, this code copies and resorts by the
second column and populated a second worksheet. and works great (thank
you
to the kind soul who gave this to me BTW)
I'm trying to modify it to bring a third column along for the ride. I
don' t
need to sort by the third column, just copy it into the second worksheet
into the same column it belongs in.

I added all references to "C" in the code below but I can't figure out
how
to simply modify this to include the third column. It looks to me like
the
range and DSTCol are beyond my abilities at present.
I "Watched" the Vars for Range in the debugger but got more of a
property
sheet than a range of cells. So I got lost.


Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol +
1))
End With

End Sub

Help?






Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default Sorting by two column and bringing third along.
Oh... Cool!
Thanks again for the assist

"Tom Ogilvy" wrote in message
...
go to row 65536 and select A65536. Hit the End Key, then the Up Arrow.

That is what is means.

--
Regards,
Tom Ogilvy


" wrote:

Thank you, I'll try that first thing tomorrow.
What does " End(xlUp) " do here?
I would welcome a less cumbersome alternative, maybe I can follow it's
flow
and understand it instead of just pasting it in.

"Tom Ogilvy" wrote in message
...
This code is pretty verbose, but since you are happy with it:

Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
' change 1 to 2
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 2))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
' change 1 to 2
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 2))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range("A2")
End With

End Sub

--
Regards,
Tom Ogilvy


" wrote:

I am using a worksheet to enter data, this code copies and resorts by
the
second column and populated a second worksheet. and works great (thank
you
to the kind soul who gave this to me BTW)
I'm trying to modify it to bring a third column along for the ride. I
don' t
need to sort by the third column, just copy it into the second
worksheet
into the same column it belongs in.

I added all references to "C" in the code below but I can't figure out
how
to simply modify this to include the third column. It looks to me like
the
range and DSTCol are beyond my abilities at present.
I "Watched" the Vars for Range in the debugger but got more of a
property
sheet than a range of cells. So I got lost.


Private Sub Worksheet_Activate()

Dim A, B, C
Dim DstCell As String
Dim DstCol As Long
Dim DstRng As Range
Dim I As Long
Dim FirstRow As Long
Dim LastRow
Dim SourceSheet As String
Dim SrcCell As String
Dim SrcCol As Long
Dim SrcRng As Range

'Variables for source and destination
SrcCell = "A2"
SourceSheet = "NumSort"
DstCell = "A2"

'Find all data entries on the source worksheet
With Worksheets(SourceSheet)
SrcCol = .Range(SrcCell).Column
FirstRow = .Range(SrcCell).Row
LastRow = .Cells(Rows.Count, SrcCol).End(xlUp).Row
Set SrcRng = .Range(SrcCell, .Cells(LastRow, SrcCol + 1))
End With

With ActiveSheet
'Copy the data from the source sheet to the destination
DstCol = .Range(DstCell).Column
LastRow = (LastRow - FirstRow) + .Range(DstCell).Row
Set DstRng = .Range(DstCell, .Cells(LastRow, DstCol + 1))
DstRng() = SrcRng()
'Reverse the data
For I = 1 To DstRng.Cells.Count
A = DstRng.Cells(I, 1).Value
B = DstRng.Cells(I, 2).Value
C = DstRng.Cells(I, 3).Value
DstRng.Cells(I, 1).Value = B
DstRng.Cells(I, 2).Value = A
DstRng.Cells(I, 3).Value = C
Next I
'Sort the data from A to Z
DstRng.Sort Key1:=.Range(.Cells(1, DstCol), .Cells(LastRow, DstCol +
1))
End With

End Sub

Help?








Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

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
match 1 column bringing in a value from another column Dot Excel Worksheet Functions 3 October 27th 07 10:34 PM
Sorting a Pivot Table Column that is not the first column... [email protected] Excel Worksheet Functions 1 October 10th 07 09:02 PM
Vlookup bringing back #N/A Afsha Excel Discussion (Misc queries) 2 July 5th 07 01:19 PM
Bringing together data Emma Hope Excel Worksheet Functions 4 May 25th 06 02:58 PM
Right column doesn't change when sorting left column. nohope Excel Discussion (Misc queries) 2 July 19th 05 03:27 PM


All times are GMT +1. The time now is 12:20 PM.

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"

 

Contact Us ExcelBanter forum home Privacy Statement