Looking for a Vba count solution

[MrStanley]

Hello, Can somebody please help me on using a Vba count method of solving the following. I'm attempting to get a football stat of how many games ago a team last Won Drew or Lost. Column A2 contains the first team and the range of B2:AU2 the results (B2 is the most recent result) at the end of the season of 46 games AU2 will be the last result.
A typical example is L L L L L W W D etc, so they last won 6 games ago, last Draw 8 games ago and last loss 1 game ago. I would like the results to go into BA2,BB2 and BC2.
If someone can get me started i'll try and apply the solution to all the teams in Col A.
Many Thanks Robert

 

[JonXL]

I think MATCH might do what you're looking to do. For example, to get the most recent win...

=MATCH("W",B2:AU2,0)

Sorry, I see you were wanting a VBA solution. If you're unable to find anything else, you can still use the formula above in a VBA Evaluate function.

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-evaluate-method-excel

 

[frank_AL]

Robert,

Give this code a try.

Option Explicit


Sub LastLoss()


Dim x As Integer
Dim y As String
Dim col As Range
Dim i As Integer
Dim lc As Integer


lc = Cells(2, Columns.Count).End(xlToLeft).Column
For x = 1 To 3
        Select Case x
            Case 1
                y = "W"
                Set col = Range("BA2")
            Case 2
                y = "D"
                Set col = Range("BB2")
            Case 3
                y = "L"
                Set col = Range("BC2")
        End Select
        
    For i = 3 To lc
        If Cells(2, i) = y Then
            col.Value = i - 2
            GoTo skip
        End If
    Next i
skip:
Next x


End Sub

 

[MrStanley]

Many Thanks JonXL, I was so fixated on a count loop solution I didn't even consider other methods, I think I need to step back sometimes and think more.

Also many thanks to frank_AL I haven't tested your solution yet but it does explain to me why I didn't get anywhere near achieving what you've written...phew!

Thanks both
Regards Robert