Help! Auto insert Data issue

[jnr_ironside]

I have a worksheet that is basically a score card for a cricket game.

I have the following code to input the score automatically:

Sub InsertScore1()
'InsertScore1 Macro

If Range("E2") = 0 Then
Else
Set c = Range("E9:AI18").Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
c.Value = Range("C2")
c.Activate
End If
End Sub

E2 is the Ball Counter, it goes from 0-6 so 0 = Start of new over
C2 is the score from the ball or the way Batsman is out (Bowled, Caught, LBW, or Stumped)
Both Change on a Button Click

E9:AI18 is my score card for 10 Batsman facing a maximum of 30 balls each (it is 31 Columns as the formula checks to see if the next cell in the range is blank so Coulumn E is empty to make sure the first value is entered in F9

I want the code to see if the active cell contains either Bowled, Caught, LBW or Stumped and if it does add the next score to the next batsman instead of just the next empty cell in the range.

So At the moment my code does this:

Batsman 1: 1 2 3 Caught 1 2 3

But I want it to do this:

Batsman 1: 1 2 3 Caught
Batman 2: 1 2 3

How could I achieve this please?

Ah... While writing this I' realised that if a Batsman faces all 30 balls then the first score for the next will go into E10 but I would need it to go in F10 and so on, how could I make sure this happens?

Thanks in advance.

 

[smiler]

hi,

Try this, note that i have used a helper cell to show which row the current batsman is in, cell "A1" should iitially be set to 9 (first batsman row) - this will increment automatically after one of the 'out' options is used

Sub InsertScore1()
'InsertScore1 Macro
currentbatrow = Range("A1")
If Range("E2") = 0 Then
Else
Set c = Range("E" & currentbatrow & ":" & "AI" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
c.Value = Range("C2")
If c.Value = "Bowled" Or c.Value = "Caught" Or c.Value = "LBW" Or c.Value = "Stumped" Then Range("a1").Value = Range("A1").Value + 1
c.Activate
End If
End Sub

 

[smiler]

Sorry, misssed the 2nd part,

This version will increment to next batsmen after 30 balls are used

Sub InsertScore1()
'InsertScore1 Macro
currentbatrow = Range("A1")
If Range("E2") = 0 Then
Else
Set c = Range("E" & currentbatrow & ":" & "AI" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
If c Is Nothing Then
Range("a1").Value = Range("A1").Value + 1 ' if no blanks left (30 balls used) then move to next player
currentbatrow = Range("A1")
Set c = Range("E" & currentbatrow & ":" & "AI" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
End If
c.Value = Range("C2")
If c.Value = "Bowled" Or c.Value = "Caught" Or c.Value = "LBW" Or c.Value = "Stumped" Then Range("a1").Value = Range("A1").Value + 1
c.Activate
End If
End Sub

 

[jnr_ironside]

Smiler, Sorry for late reply but internet access is sporadic this week. Thanks very much, makes sense now I see it written, don't have access to ny file until the weekend but will try it then. will post back then.

 

[jnr_ironside]

Smiler,

Job Done! Thanks so much for your help.