ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Sorting by two column and bringing third along. (https://www.excelbanter.com/excel-programming/357278-sorting-two-column-bringing-third-along.html)

No Name
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?



Tom Ogilvy
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?




No Name
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?

Tom Ogilvy
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?

No Name
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?


All times are GMT +1. The time now is 05:02 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com