Peter Cresswell
New Member
- Joined
- May 20, 2018
- Messages
- 2
[TABLE="width: 256"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Hi Everyone,
Although I have used Excel to produce simple spread sheets, I am new to using VBA and macros. I am struggling with creating a macro that will do a fairly simple task and I have found a YouTube video that seems to do what I want but having created it by heavily copying from the video it doesn't run against my data table.
The data table has simply two columns Column A records a name and Column B records a time in seconds to 1/100 th of a second. This is for a slot car racing feature at a local charity fete and records and sorts the times and the names with them automatically when a new name and time are added. This is an example of the table.
[TABLE="width: 256"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Time (seconds)
[/TD]
[/TR]
[TR]
[TD]George
[/TD]
[TD]20.12
[/TD]
[/TR]
[TR]
[TD]Don
[/TD]
[TD]17.64
[/TD]
[/TR]
[TR]
[TD]Pete
[/TD]
[TD]21.35
[/TD]
[/TR]
[TR]
[TD]Nick
[/TD]
[TD]13.98
[/TD]
[/TR]
[TR]
[TD]allan
[/TD]
[TD]17.63
[/TD]
[/TR]
[TR]
[TD]fred
[/TD]
[TD]16.46
[/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]18.27
[/TD]
[/TR]
[TR]
[TD]david
[/TD]
[TD]19.04
[/TD]
[/TR]
[TR]
[TD]Ian
[/TD]
[TD]17.37
[/TD]
[/TR]
[TR]
[TD]fred 2
[/TD]
[TD]16.14
[/TD]
[/TR]
</tbody>[/TABLE]
Note the table can be either as long as necessary or have a limit of say top 25 times providing the slowest times drop off the bottom. New data would be added on the next Row.
When a new row is added (name in Col A and time in Col B), when I press 'Enter' I want the table to sort the new data into its position in the current table based on Col B. Below is the macro I have, which as I mentioned above has been heavily borrowed from a youtube video, but my needs are to have a name (i.e alpha characters) in col A whereas the one in the video used numeric.
****Macro***
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
Dim lastRow As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:B"&lastrow).Sortkey1:=Range("B2:B"&lastRow),order1:=xlAscending,Header:=xlNo
End If
End Sub
******
A new line of data will be added manually, and on pressing ‘Enter’ I want the table to auto sort with with the lowest time in Col B (and associated name from Col A) to be added to the table in it’s ranking order
The video on YouTube can be viewed at: https://www.bing.com/videos/search?...E5E308D14561DF600E2BE&view=detail&FORM=VIREHT
Hopefully someone here can help me progress this. I have spent a few days trying to make this work without luck so far! I’m now getting desperate as I need it for Saturday!!!!
Many thanks
Pete
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Hi Everyone,
Although I have used Excel to produce simple spread sheets, I am new to using VBA and macros. I am struggling with creating a macro that will do a fairly simple task and I have found a YouTube video that seems to do what I want but having created it by heavily copying from the video it doesn't run against my data table.
The data table has simply two columns Column A records a name and Column B records a time in seconds to 1/100 th of a second. This is for a slot car racing feature at a local charity fete and records and sorts the times and the names with them automatically when a new name and time are added. This is an example of the table.
[TABLE="width: 256"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Time (seconds)
[/TD]
[/TR]
[TR]
[TD]George
[/TD]
[TD]20.12
[/TD]
[/TR]
[TR]
[TD]Don
[/TD]
[TD]17.64
[/TD]
[/TR]
[TR]
[TD]Pete
[/TD]
[TD]21.35
[/TD]
[/TR]
[TR]
[TD]Nick
[/TD]
[TD]13.98
[/TD]
[/TR]
[TR]
[TD]allan
[/TD]
[TD]17.63
[/TD]
[/TR]
[TR]
[TD]fred
[/TD]
[TD]16.46
[/TD]
[/TR]
[TR]
[TD]john
[/TD]
[TD]18.27
[/TD]
[/TR]
[TR]
[TD]david
[/TD]
[TD]19.04
[/TD]
[/TR]
[TR]
[TD]Ian
[/TD]
[TD]17.37
[/TD]
[/TR]
[TR]
[TD]fred 2
[/TD]
[TD]16.14
[/TD]
[/TR]
</tbody>[/TABLE]
Note the table can be either as long as necessary or have a limit of say top 25 times providing the slowest times drop off the bottom. New data would be added on the next Row.
When a new row is added (name in Col A and time in Col B), when I press 'Enter' I want the table to sort the new data into its position in the current table based on Col B. Below is the macro I have, which as I mentioned above has been heavily borrowed from a youtube video, but my needs are to have a name (i.e alpha characters) in col A whereas the one in the video used numeric.
****Macro***
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Then
Dim lastRow As Long
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:B"&lastrow).Sortkey1:=Range("B2:B"&lastRow),order1:=xlAscending,Header:=xlNo
End If
End Sub
******
A new line of data will be added manually, and on pressing ‘Enter’ I want the table to auto sort with with the lowest time in Col B (and associated name from Col A) to be added to the table in it’s ranking order
The video on YouTube can be viewed at: https://www.bing.com/videos/search?...E5E308D14561DF600E2BE&view=detail&FORM=VIREHT
Hopefully someone here can help me progress this. I have spent a few days trying to make this work without luck so far! I’m now getting desperate as I need it for Saturday!!!!
Many thanks
Pete
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]