sort from high to low with VBA

Status
Not open for further replies.

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
Hello everyone,

I have a coinflip macro which inputs a row of data.
What I noticed after using it for over 200 times is that I have to scroll pretty far down everytime if I want to simply check what it input.
Can I add in my macro that after it inputs the new data, its going to sort column A from Z-A so the new input gets on the top.
Here's how it looks like:
1604794864181.png

This thing continues to row 242 rn^
So I input my information in B1-C1-D1 and then the macro automatically splits that information and puts it in a new row on the bottom.
my code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Application.EnableEvents = False
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "C").Value = Cells(Rw, "C").Value
    Cells(Rw, "C").NumberFormat = "0"
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    Cells(Rw, "G").NumberFormat = "\$ 0;\$ -0"
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    Cells(Rw, "A").Resize(, 8).Font.Bold = True
    [B1:D1] = ""
    Range("B1").Select
    Application.EnableEvents = True
  End If
End Sub

I should also mention that I'm nowhere good enough to even understand my own code. This code was given to me at some point in 2019.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
255
Office Version
  1. 2019
Platform
  1. Windows
Or should I look at making a completely new macro that sorts the thing? but then again I don't know how to make that.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Judging by your our thread has this been resolved?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
As no reply I assume the answer is yes.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,127,741
Messages
5,626,610
Members
416,195
Latest member
tonmcg

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
Top