Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with VBA
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with VBA
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 -----Original Message----- Hello all, I'm fair with Excel but know nothing of VBA. I'm hoping someone will help me to get started. I think I need a UDF to compute the Season Win/Lose Streaks in the simple attached file. Weeks 1 through 3 are given and I've inserted the correct W/L Streak answers so as to describe the problem... but I have no idea how to code it. Thanks for even looking at it. Attachment filename: bjl test wl.xls Download attachment: http://www.excelforum.com/attachment.php?postid=593262 --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with VBA
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 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----- Hello all, I'm fair with Excel but know nothing of VBA. I'm hoping someone will help me to get started. I think I need a UDF to compute the Season Win/Lose Streaks in the simple attached file. Weeks 1 through 3 are given and I've inserted the correct W/L Streak answers so as to describe the problem... but I have no idea how to code it. Thanks for even looking at it. Attachment filename: bjl test wl.xls Download attachment: http://www.excelforum.com/attachment.php?postid=593262 --- Message posted from http://www.ExcelForum.com/ . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with VBA
Thanks John,
I really appreciate your help... BJL --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|