Peter Cresswell
New Member
- Joined
- May 20, 2018
- Messages
- 2
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.
<tbody> </tbody> 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 | |||||||||||||||||||||||
<colgroup><col><col></colgroup><tbody>
</tbody>