Hi,
I'm affraid I bit off more than I can chew and It's a while since I programmed.
1/ The routine is set in an open Excel worksheet which has 5 fields/columns per row
2/ The routine reads 1 row at a time, splits 1 field, does some ifs and appends 6 new fields per row
3/ I'm stuck with the Loop
4/ I'm stuck with the IFs especially bracketing
5/ I'm stuck with the output writes
I'd really appreciate if you could just wiiiizzz through it and ellimate the mistakes. For you 5 mins for me 5 days.
Here it is ....
Sub TeamPerformance01()
' TeamPerformance01
' Name Fields in open worksheet
ActiveWorkbook.Names.Add Name:="Date", RefersToR1C1:="=Sheet1!C1"
Columns("A:A").Select
ActiveWorkbook.Names.Add Name:="Time", RefersToR1C1:="=Sheet1!C2"
Columns("B:B").Select
ActiveWorkbook.Names.Add Name:="Team_Code", RefersToR1C1:="=Sheet1!C3"
Columns("C:C").Select
ActiveWorkbook.Names.Add Name:="Score", RefersToR1C1:="=Sheet1!C4"
Columns("D:D").Select
ActiveWorkbook.Names.Add Name:="Points", RefersToR1C1:="=Sheet1!C5"
Columns("E:E").Select
' typical record looks like 20060101 09:09:09 10JB45 926 101 not formated
' set up fields that will be output with examples
Dim Team_Member As Variant '10
Dim Team As String '"JB"
Dim Time_Played As Variant '45
Dim JB_Total As Integer '05
Dim CK_Total As Integer '04
Dim KK_Coach As Integer '04
Dim Total_Time As Integer '325
' names new fields - maybe !!!
ActiveWorkbook.Names.Add Name:="Team_Member", RefersToR1C1:="=Sheet1!C6"
Columns("F:F").Select
ActiveWorkbook.Names.Add Name:="Team", RefersToR1C1:="=Sheet1!C7"
Columns("G:G").Select
ActiveWorkbook.Names.Add Name:="Time_Played", RefersToR1C1:="=Sheet1!C8"
Columns("H:H").Select
ActiveWorkbook.Names.Add Name:="JB_Total", RefersToR1C1:="=Sheet1!C9"
Columns("I:I").Select
ActiveWorkbook.Names.Add Name:="CK_Total", RefersToR1C1:="=Sheet1!C10"
Columns("J:J").Select
ActiveWorkbook.Names.Add Name:="KK_Coach", RefersToR1C1:="=Sheet1!C11"
Columns("K:K").Select
ActiveWorkbook.Names.Add Name:="Total_Time", RefersToR1C1:="=Sheet1!C12"
Columns("L:L").Select
' *$%@*""!! THIS IS WHERE I AM LOST .......
' read rows 1 by 1, process each 1, and append results in new fields to that row
for row(i) to eof
' reset counters
Team_Member = 0
Team = " "
Time_Played = 0
JB_Total = 0
CK_Total = 0
KK_Coach = 0
Total_Time = 0
' select Date Time Team_Code Score Points ' from open worksheet
date.select
time.select
Team_Code.select
score.select
points.select
' strip out 3 fields from Team_Code
Team_Member = Mid(Team_Code, 1, 2)
Team = Mid(Team_Code, 3, 2)
Time_Played = Mid(Team_Code, 5, 2)
'simple little additions depending on values stripped from Team_Code
if Team = 'JB' then (JB_Total = JB_Total + 1 and KK_Coach = KK_Coach + 1)
if Team = 'CK' then (CK_Total = CK_Total + 1 and (KK_Coach = KK_Coach + 1)
' simple little additions I am struggling with comparisons and bracketing
If Time_Played >= 60 Then Time_Total = Time_Total + 60
If Time_Played < 60 and >= 30 Then Time_Total = Time_Total + 30
' continues litte additions I am struggling with bracketing
If (Time_Played < 30 and Team_Member = 04) or
(Time_Played < 30 and Team_Member = 05) or
(Time_Played < 30 and Team_Member = 06)
then Time_Total = Time_Total + 60
End If
' write/ append the following fields to the row and move onto next row
' though this would happen automatically !! :)
Team_Member
Team
Time_Played
JB_Total
CK_Total
KK_Coach
Total_Time
' and move onto next row
If Not EOF Then i = i + 1
End Sub
Trying to write a VERY VERY simple routine BUT GOT STUCK !! ::) = Brain Ache !!
Dietz
Please do not Cross-post the same question to all the forums that mention VB.
From looking at the code - it looks like VBA code for Excel.
If this is the case then use the forum
http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=74&SiteID=1
which is used to answer VBA questions. This forum is for VB.NET questions. VB.NET is a very different product from VBA and your question is bast asked in the VBA Forum.
KSpeed
Hello,
I started to write some code for you but then saw that CK total and JK total will always be 1 as you set them to 0 all the time.
Where are you going to store the running totals
If you want these to be running totals in each row, you need to change the sheet a bit.
Shall I just do what I think you mean
Chas.
In the meantime I'd get a book on excel VBA too
MuscleHead
Still not sure about the "Total Time", but this will get you on your way.
Chas
Code start
Sub test()
Dim teamMbr As String
Dim team As String
Dim timePlayed
Dim JBttl As Integer
Dim CKttl As Integer
Dim KKcoach As Integer
Dim totalTime As Integer
Dim teamCode As String
Dim thisCell As String
Dim off As Integer
' select first row exluding headers 'e Cell A2
Range("A2").Select
thisCell = Selection.Offset(off, 0) 'read contents of this cell
Do While thisCell <> "" ' do while thicell contains something
teamCode = Selection.Offset(off, 2) ' 3rd column
teamMbr = Mid(teamCode, 1, 2) ' 1st two chars of team code
team = Mid(teamCode, 3, 2) ' 3rd and 4th chars of team code
timePlayed = Val(Mid(teamCode, 5, 2)) ' 5th and 6th chars of teamcode
JBttl = Val(Selection.Offset(off - 1, 8))
CKttl = Val(Selection.Offset(off - 1, 9))
KKttl = Val(Selection.Offset(off - 1, 10))
Select Case team
Case "JB"
JBttl = JBttl + 1 'if team JB add 1 to 4their total
Case "CK"
CKttl = CKttl + 1 'if team CK add 1 to their total
End Select
KKttl = KKttl + 1 ' in both cases add 1 to KKcoach total
Select Case timePlayed
Case Is >= 60
totalTime = totalTime + 60
Case 30 To 60
totalTime = totalTime + 30
Case Is < 30
Select Case teamMbr
Case "04", "05", "06"
totalTime = totalTime + 60
End Select
End Select
' write these values to sheet
Selection.Offset(off, 5) = teamMbr
Selection.Offset(off, 6) = team
Selection.Offset(off, 7) = timePlayed
Selection.Offset(off, 8) = JBttl
Selection.Offset(off, 9) = CKttl
Selection.Offset(off, 10) = KKttl
off = off + 1 ' add one to offset ie to read next row
Loop
End Sub
code ends
Good luck,
Chas
Ryan_H