New to VBA - help required please.

[Neil Grantham]

Hi folks,

I have been given assistance with some VBA code to calculate a winning
or unbeaten streak in my spreadsheet - it counts the W (Win) and D
(Draw) occurences in a column of the sheet.
Sub WinLossStreak()
Dim wks As Worksheet
Dim rng As Range
Dim rngBeginCheckingHere As Range
Dim intWinStreak As Integer
Dim intBestStreak As Integer

Set wks = ThisWorkbook.Worksheets("Matches")
Set rngBeginCheckingHere = wks.Range("E2")

intWinStreak = 0
For Each rng In wks.Range(rngBeginCheckingHere, _
Cells(Rows.Count, rngBeginCheckingHere.Column).End(xlUp))
If rng.Value = "W" Or rng.Value = "D" Then
intWinStreak = intWinStreak + 1
' Check the current win streak vs.
' the best win streak
If intBestStreak < intWinStreak Then
intBestStreak = intWinStreak
End If
Else
' Current record is a Loss, reset
' the win streak.
intWinStreak = 0
End If
Next rng
MsgBox intBestStreak
End Sub

I have been able to input this OK, and found that using F5 whilst in
the VBA editor, will go to my spreadsheet, and give me a pop-up box
with the answer.

However, I would like this to be automatically updated on my sheet, so
that when I put in new results as a season progresses, this is part of
my stats.

So far, I am using functions such as COUNTIF in my Stats panel to show
number of Wins, Draws and losses etc. but would like to include a Best
Winning streak stat too.

Thus, my question is this, how do I get the result of the above code
to be returned automatically to a cell.

I apologise if this is very rudimentary, but I can't seem to find what
I'm looking for

Thanks
Neil

 

[Bob Phillips]

Neil,

Instead of
MsgBox intBestStreak

use
Worksheets("Sheet1").Range("A1").Value = intBestStreak

change the sheetname and cell to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Neil Grantham]

Thanks Bob

I've tried that, and it seemed to work if I updated from the VB editor
(F5), but if I update the sheet results, the cell this applies to
doesn't!
Did I do something wrong?

Neil

 

[Bob Phillips]

Neil,

What is triggering this macro from the cell? In other words, what are you
doing, and what do you expect that doesn't happen?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)