Excel VBA - Win Lose Streaks

[belblanco]

Could someone put the code I need in this spreadsheet? I would like t
determine the Win/Lose Streaks of four teams, A - D and show that o
Sheet 1. Sheet 2 contains the individual game results. I want t
create a subprogram that loops through the array on Sheet 2, comput
the answers and display them on Sheet 1. To help describe this, I als
show what the correct answers are on sheet 1.

I'm reading books and stuff to try to figure it out but I'm totall
confused at this point. I really need someone to show me how it's don
right so I can learn properly... thanks

Attachment filename: wlsample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61435

 

[John]

Belblanco I posted the code the last time you asked.

John

 

[belblanco]

I'm sorry John, I couldn't get it to work and don't know enough abou
it to debug it. Thanks though

 

[John]

I will try once again - it works fine for me.

John

spreadsheet set up is as follows:

column A - weeks
column B - team names
column C - Win
column D - Loss

(like this)

col A col B col C col D
Win Loss
Week1 TeamA 1
TeamB 1

etc

data is assumed to be on sheet1

a range called summary is defined where the summary of
results will be posted.

Teams A thru D are listed in left hand column of this range

a range called results is defined which includes the team
names, wins, and losses

4 teams are allowed



Sub Macro1()
'
' Macro1 Macro
'
Dim rng As Range
Dim team(4) As Variant
Dim win(4) As Integer
Dim loss(4) As Integer
Sheets("sheet1").Activate
bottomrow = ActiveSheet.Cells(Rows.Count, _
Range("results").Column).End(xlUp).Row
toprow = ActiveSheet.Cells(1, "B").End(xlDown).Row
sumrow = Range("summary").Row
sumcol = Range("summary").Column
For i = 1 To 4
win(i) = 0
loss(i) = 0
For j = toprow To bottomrow
If Cells(j, 2).Text = _
Cells(i + sumrow, sumcol).Text Then GoTo found _
Else: GoTo nextj
found:
If Cells(j, 3) > 0 Then GoTo win
win(i) = 0
loss(i) = loss(i) + 1
GoTo nextj
win:
win(i) = win(i) + 1
loss(i) = 0
nextj:
Next j
Next i
For i = 1 To 4
Cells(i + sumrow, sumcol + 1).Value = win(i)
Cells(i + sumrow, sumcol + 2).Value = loss(i)
Next i
End Sub

 

[John]

I took a look at your sheet and see you have team names in
column A, wins in C, and losses in D. based on that

change

Cells(j, 2).Text to Cells(j, 1).Text

name your summary range to summary

and try that

John

 

[belblanco]

John,

I'm running Excel 97. I have defined the range names (summary an
results) in sheet 1 as you instructed. I have copied and pasted you
code into module 1 of sheet 1. When I compile, I get the following:

Run-time error '13' - Type mismatch

on the following line:

If Cells(j, 3) > 0 Then GoTo win

Also, two lines above that you have

Else: GoTo nextj

Could this be related to the problem? I don't want to change anythin
unless you tell me to... since you said it works for you.

Thanks, belblanco

 

[John]

Are the 1's indicating win or loss text or numbers? If
not numbers, make them so.

Otherwise

Try hard coding in the row numbers of the first win or
loss and the last win or loss (in the results), like
below - I just made up 20 and 5 as row #'s. If that fixes
it then let me know exactly how your sheet is laid out.

bottomrow = 20
toprow = 5

John

 

[belblanco]

No, I'm using the original sheet that you first wrote the code for.
I've reattached it to be sure.

In sheet 1 of the attached file I have a name defined as results at
=Sheet1!$B$2:$D$13 and a name defined as summary at
=Sheet1!$B$15:$D$18. Is that what you expected

Attachment filename: bjl test wl.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61468

 

[Guest]

OK

1) Move (cut and paste) the summary range out to column F
or so (somewhere not below the results range).

2) In the results range make sure the blank cells are
empty (delete contents) or zero - I think you had empty
text cells which caused the type mismatch - when I deleted
the cell contents that went away. Easiest way to do this
is to make the loss column cells 1 minus the in column
cells, and just enter one or zero in the win column.

3) redefine the summary range to include the header row
the row with Win Streak). I did not explicitly say this
before - my fault.

John

 

[belblanco]

OK,

I moved the summary out like you said and I redefined the summer
range. I also made sure the results range was formatted as number an
I deleted the contents that were not 1.

The program now compiles clean with the following results:

...............................W-Streak.....L-Streak
Season......Team A.........0..................1
................Team B.........0..................2
................Team C.........0..................2
................Team D.........2..................0

Team A is correct but B should be 1 win, C should be 3 losses, and
should be 3 wins.

We're making progress

 

[duane]

I know my file works! Joh

Attachment filename: win_lose streak.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61482

 

[belblanco]

I think I can figure it out from here... probably some stupid thing
can't see. You helped me out a lot John, thanks

 

[belblanco]

I got it now John. I had defined the range "results" different tha
what you had. How would I display the summary table on sheet 2 instea
of sheet 1

 

[duane]

just cut and paste it there. the named range should move! that's on
of the nice things about using range names as references.

Joh

 

[Simon Lloyd]

Hi Duane, Belblanco!!

I downloaded your win/lose streak workbooks to have alook at what yo
were trying to do and the fixes you both used.....butwhen i downloade
them i got a little something extra from one of you.............in th
VBA editor i now have AUTOSAVE.XLS(AUTOSAVE.XLA) and its passwor
protected!!! can who ever this belongs to supply me with the passwor
so i may remove it please!

Thanks,

Simon

 

[belblanco]

Hi Simon,

What is the status of the problem you were having with autosave.xla.
I'm worried that I may have caused problems for you but I can't se
where did. I don't use the add-in, therefore I didn't have a passwor
for you. John/Duane and I have called the file up numerous time
without any problems or requests to enter passwords. I'm wondering i
someone else could have loaded the file with something that would caus
this. It concerns me that it may not be wise to attach files in thi
forum if that is possible. Anyway, I hope you're fixed now.

belblanc