VBA Code - Taking notice of only the last 5 entries of a certain value in a table

Hello.

I've got a table of UK football (soccer for you US-based folk) results which shows Fixture Date, Home Team, Away Team, Home Goals, Away Goals (last two therefore give the final score), and Result (which lists either H, D or A for Home Win, Draw or Away Win).

I've written a code so that I can check the overall form of every team (by this, I mean it analyses all the results of the team that is entered in a certain cell and delivers how many Wins, Draws and Losses they have had in all their games so far). To do this, I use the code below (thanks to bmouse for helping with this in a different thread):

---------

Dim counterW As Integer
Dim counterL As Integer
Dim counterD As Integer
Dim StartNumber As Integer
Dim EndNumber As Integer

EndNumber = 1000
counterW = 0
counterL = 0
counterD = 0

For StartNumber = 2 To EndNumber

If Cells(2, "A") = Cells(StartNumber, "J") Then

If Cells(StartNumber, "N") = "H" Then

counterW = counterW + 1

Else

If Cells(StartNumber, "N") = "D" Then

counterD = counterD + 1

Else: counterL = counterL + 1
End If
End If
End If

Next StartNumber



For StartNumber = 2 To EndNumber

If Cells(2, "A") = Cells(StartNumber, "K") Then

If Cells(StartNumber, "N") = "A" Then

counterW = counterW + 1

Else

If Cells(StartNumber, "N") = "D" Then

counterD = counterD + 1

Else: counterL = counterL + 1


End If
End If
End If

Next StartNumber

Cells(2, "C").Value = counterW
Cells(2, "D").Value = counterD
Cells(2, "E").Value = counterL

End Sub

-------

This works great, but now what I want to do is to do a FORM table; that is, a table which shows their Wins/Draws/Losses in just their last 5 games.

Is there some kind of VBA code I can use to alter the above in order to take only their last 5 results?

Thanks in advance!

You're going to be in the naughty book for not wrapping your code in tags # in reply box
It would be better if you attach a sample workbook so we don't have to muck about trying to recreate your workbook

My way of doing this would be this. Not very efficient, I know, but nothing better comes to mind yet.

Put auto-filters on your data range and filter out scores for 1 team as Home Team, copy/paste that data to another sheet. Remove the auto-filter, then put the filter on Away Team and filter data for the same team. Copy/paste the data to the other sheet. Sort the copied data by date (I have game dates in my custom-made list, not sure about your data, though), and keep the data for the last 5 dates, remove the rest.

After that, I suppose you could tweak the macro you've been using to calculate the necessary results and percentages.

The problem is that this is time consuming, as you would have to filter out data for each team and keep the data seperate, otherwise the macro will provide inaccurate results.

I'll think about this some more, for now this is the best advice I can give. Good luck

Originally Posted by bmouse

My way of doing this would be this. Not very efficient, I know, but nothing better comes to mind yet.

Put auto-filters on your data range and filter out scores for 1 team as Home Team, copy/paste that data to another sheet. Remove the auto-filter, then put the filter on Away Team and filter data for the same team. Copy/paste the data to the other sheet. Sort the copied data by date (I have game dates in my custom-made list, not sure about your data, though), and keep the data for the last 5 dates, remove the rest.

After that, I suppose you could tweak the macro you've been using to calculate the necessary results and percentages.

The problem is that this is time consuming, as you would have to filter out data for each team and keep the data seperate, otherwise the macro will provide inaccurate results.

I'll think about this some more, for now this is the best advice I can give. Good luck

Thanks for the reply.

Unfortunately I'm looking for something that can be done on a large scale to calculate many teams at once (as you've helped me with previously on Home Form/Overall Form in previous threads ).

Maybe it isn't possible, in which case I'll tweak my formula to remove the 'Form' part if necessary. Let me know if you have any other ideas!

Thanks.