Modify Macro to go from Ascending to Descending Order

Alphacsulb

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
414
I found this Macro and it works for what I need. Except that the results end up in Ascending Order and I would prefer it to end up in Descending Order.

Any Help?

Code: 
Private Sub CommandButton1_Click()
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
For I = 1 To 61 '61 being the last row with data in it
J = Worksheets("Sheet1").Range("C" & Trim(Str(I))).Value
K = Worksheets("Sheet1").Range("D" & Trim(Str(I))).Value
For L = J To K 'now we're going to write the values out to Sheet 2
Worksheets("Sheet2").Range("A" & Trim(Str(L))).Value = Worksheets("Sheet1").Range("A" & Trim(Str(I))).Value
Worksheets("Sheet2").Range("B" & Trim(Str(L))).Value = 1
Worksheets("Sheet2").Range("C" & Trim(Str(L))).Value = Trim(Str(L))
Worksheets("Sheet2").Range("A" & Trim(Str(L))).Value = "PCT:" & Worksheets("Sheet1").Range("A" & Trim(Str(I))).Value
Worksheets("Sheet2").Range("B" & Trim(Str(L))).Value = "BT:" & Worksheets("Sheet1").Range("B" & Trim(Str(I))).Value
Next L
Next I
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Code: 
Private Sub CommandButton1_Click() 
    Dim I As Long 
    Dim J As Long 
    Dim K As Long 
    Dim L As Long 
    Dim WS1 As Worksheet 
    Dim WS2 As Worksheet 
    Set WS1 = Worksheets("Sheet1") 
    Set WS2 = Worksheets("Sheet2") 
    For I = 61 To 1 Step -1 '61 being the last row with data in it
        With WS1 
            J = .Range("C" & I) 
            K = .Range("D" & I) 
        End With 
        For L = J To K 'now we're going to write the values out to Sheet 2
            With WS2 
                .Range("A" & L).Value = WS1.Range("A" & I).Value 
                .Range("B" & L).Value = 1 
                .Range("C" & L).Value = L 
                .Range("A" & L).Value = "PCT:" & WS1.Range("A" & I).Value 
                .Range("B" & L).Value = "BT:" & WS1.Range("B" & I).Value 
            End With 
        Next L 
    Next I 
End Sub

This is more of what I was thinking of but when it sorts in sheet 2 it is only sorting by column C, not the entire row. I want to sort by Column C but also link with the rest of the columns.

Hope that makes sense.;)
 
Upvote 0
does this do what you expect?
Rich (BB code): 
Private Sub CommandButton1_Click() 
    Dim I As Long 
    Dim J As Long 
    Dim K As Long 
    Dim L As Long 
    Dim WS1 As Worksheet 
    Dim WS2 As Worksheet 
    Set WS1 = Worksheets("Sheet1") 
    Set WS2 = Worksheets("Sheet2") 
    For I = 1 To 61 '61 being the last row with data in it
        With WS1 
            J = .Range("C" & I) 
            K = .Range("D" & I) 
        End With 
        For L = J To K 'now we're going to write the values out to Sheet 2
            With WS2 
                .Range("A" & L).Value = WS1.Range("A" & I).Value 
                .Range("B" & L).Value = 1 
                .Range("C" & L).Value = L 
                .Range("A" & L).Value = "PCT:" & WS1.Range("A" & I).Value 
                .Range("B" & L).Value = "BT:" & WS1.Range("B" & I).Value 
            End With 
        Next L 
    Next I 
    Range("a" & J & ":c" & K).Sort key1:=Range("c" & J), _
                order1:=Ascending, Header:=xlGuess
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
Locking/unlocking a cell with a Combo Box value Mouse click problem
Replies
0
Views
497
XrayLemi
X
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
391
sspatriots
S
W
  • Question
Using goal seek within VBA
Replies
0
Views
672
waptrick
W
S
  • Question
Worksheet Change Event
Replies
2
Views
389
sspatriots
S
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
351
sspatriots
S

Forum statistics

Threads
1,215,357
Messages
6,124,482
Members
449,165
Latest member
ChipDude83

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top