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
--
Message posted from http://www.ExcelForum.com

Belblanco I posted the code the last time you asked.

John


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

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

Attachment filename:
wlsample.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=614352
---
Message posted from http://www.ExcelForum.com/

.

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

--
Message posted from http://www.ExcelForum.com

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



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


---
Message posted from http://www.ExcelForum.com/

.

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


-----Original Message-----
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



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


---
Message posted from http://www.ExcelForum.com/

.

.

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

--
Message posted from http://www.ExcelForum.com

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
-----Original Message-----
John,

I'm running Excel 97. I have defined the range names
(summary and
results) in sheet 1 as you instructed. I have copied and
pasted your
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 anything
unless you tell me to... since you said it works for you.

Thanks, belblanco.


---
Message posted from http://www.ExcelForum.com/

.

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
--
Message posted from http://www.ExcelForum.com

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






-----Original Message-----
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=614680
---
Message posted from http://www.ExcelForum.com/

.

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

--
Message posted from http://www.ExcelForum.com

I know my file works! Joh

Attachment filename: win_lose streak.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61482
--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com