Need help with VBA

[belblanco]

Hello all,

I'm fair with Excel but know nothing of VBA. I'm hoping someone wil
help me to get started. I think I need a UDF to compute the Seaso
Win/Lose Streaks in the simple attached file. Weeks 1 through 3 ar
given and I've inserted the correct W/L Streak answers so as t
describe the problem... but I have no idea how to code it. Thanks fo
even looking at it

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

 

[John]

Here you go....game results are assumed in a
range "results". Range for summary is assumed
as "summary" with the team names listed in rows 2-5 of
this range. Worksheet is left as "sheet1".

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, _
"B").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]

Oh =- i should have changed the "B" in the bottomrow
definition to range("results").column.


I had assumed it was in column B....

-----Original Message-----
Here you go....game results are assumed in a
range "results". Range for summary is assumed
as "summary" with the team names listed in rows 2-5 of
this range. Worksheet is left as "sheet1".

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

I changed it here

 

[belblanco]

Thanks John,

I really appreciate your help... BJL