New to VBA - help required please.
 

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

New to VBA - help required please.
 

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" wrote in message
om...
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

New to VBA - help required please.
 

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" wrote in message ...
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)

New to VBA - help required please.
 

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)

"Neil Grantham" wrote in message
om...
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" wrote in message
...
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)