Modify Macro to go from Ascending to Descending Order

[Alphacsulb]

Any help would be appreciated.

Thanks.

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

[Simon Lloyd]

For I = 61 To 1 step -1'61 being the last row with data in it

[mdmackillop]

Use Long instead of Integer. Excel does this when it compiles
If I etc are Long or Integer, there can be no spaces to trim, "A" & I will form a valid address without any other modification.
Make use of Variables for your worksheets. Keeps things cleaner; also With statements.


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

[Alphacsulb]

Use Long instead of Integer. Excel does this when it compiles
If I etc are Long or Integer, there can be no spaces to trim, "A" & I will form a valid address without any other modification.
Make use of Variables for your worksheets. Keeps things cleaner; also With statements.


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



How would I modify this so that when it sorts by Column C it is also linking the entire row not sure the column?

[Simon Lloyd]

Alphacsulb, if you must cross post then you must supply a link, read the link in my signature! crossposted here (http://www.mrexcel.com/forum/showthread.php?t=312738)..........now for the more serious matter!
You have taken the help given here and passed it off as your own at MrExcel, you used mdmackillops last suggestion to ask Jindon for further help!

Apologies in both forums please!

[mdmackillop]

Thanks Simon

[Alphacsulb]

Thanks Simon
'I am a VBA newb, I never intended to pass it off as my own. My apologies if I may have made it seem that way.

[Simon Lloyd]

Alphacsulb, thanks for complying, almost every member in the VBA/Office help forums throughout the internet frequent other like forums and as such will always spot a cross post, if you read the link in my signature you will understand why its frowned on when not performed correctly, the usual course of action by "would be helpers" is that they will often ignore or stop giving help because they feel their valuable free time is being wasted.
Again thanks for acknowledging in both forums.