Object Variable or With Block variable not set error message

[SunderlandUK]

Hi

I'm trying to use Excel as a cricket game and have found snippets of cricket related code on the internet and it seems to work. A random number is generated in cell C2 and is allocated to a batsman (bat number in cell M9). The corresponding numbers are entered in a table in a row next to their name. However, I would like to have this table in Sheet 2 - when I try and adapt the code, I get a "Object variable or With Block variable not set" error message for the code line c.value = Range("C2"). I don't know hoe to fix this.

Sub InsertScore()

currentbatrow = Range("M9")
If Range("C6") = 10 Then
Else

Set c = Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
If c Is Nothing Then
Range("M9").Value = Range("M9").Value + 1

currentbatrow = Range("M9")

Set c = Range("E" & currentbatrow & ":" & "R" & 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("M9").Value = Range("M9").Value + 1

c.Activate
End If

End Sub

 

[Yongle]

try this

Sub InsertScore()
    With Sheets("Sheet2")
        currentbatrow = .Range("M9")
        If .Range("C6") = 10 Then
        Else
            On Error Resume Next
            Set c = .Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
            On Error GoTo 0
            If c Is Nothing Then
                .Range("M9").Value = .Range("M9").Value + 1
                currentbatrow = .Range("M9")
                On Error Resume Next
                Set c = .Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
                On Error GoTo 0
            End If

            c.Value = .Range("C2")
            If c.Value = "Bowled" Or c.Value = "Caught" Or c.Value = "LBW" Or c.Value = "Stumped" Then Range("M9").Value = Range("M9").Value + 1
            c.Activate
        End If
    End With
End Sub

 

[SunderlandUK]

Thanks for your reply

Got an error message of 'Activate method of Range class failed' for the code = c.Activate

 

[Yongle]

Try
Replacing

c.Activate

With

If Not c Is Nothing Then
    c.Parent.Activate
    c.Activate
End If

 

[SunderlandUK]

<

Try
Replacing

c.Activate

With

If Not c Is Nothing Then
    c.Parent.Activate
    c.Activate
End If

That certainly stopped the error message. The cursor went to cell F1 on sheet 2 but inserted no value and would not move

 

[Yongle]

post the whole of your sub if you want further help
thanks

 

[SunderlandUK]

Original Code -

Sub InsertScore()

currentbatrow = Range("M9")
If Range("C6") = 10 Then
Else
Set c = Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
If c Is Nothing Then
Range("M9").Value = Range("M9").Value + 1
currentbatrow = Range("M9")
Set c = Range("E" & currentbatrow & ":" & "R" & 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("M9").Value = Range("M9").Value + 1

c.Activate
End If

End Sub

 

[Yongle]

I am puzzled that you posted the original code - how does that help me solve your current issue?

 

[SunderlandUK]

Sorry - new code

Sub InsertScore2()
    With Sheets("Sheet2")
        currentbatrow = .Range("M9")
        If .Range("C6") = 10 Then
        Else
            On Error Resume Next
            Set c = .Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
            On Error GoTo 0
            If c Is Nothing Then
                .Range("M9").Value = .Range("M9").Value + 1
                currentbatrow = .Range("M9")
                On Error Resume Next
                Set c = .Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
                On Error GoTo 0
            End If

            c.Value = .Range("C2")
            If c.Value = "Bowled" Or c.Value = "Caught" Or c.Value = "LBW" Or c.Value = "Stumped" Then Range("M9").Value = Range("M9").Value + 1
            If Not c Is Nothing Then
                c.Parent.Activate
                c.Activate
                End If
            End If
    End With
End Sub

 

[Yongle]

Your code runs for me without any issue
What does the new message box return

Sub InsertScore2()
    With Sheets("Sheet2")
        currentbatrow = .Range("M9")
        If .Range("C6") = 10 Then
        Else
            On Error Resume Next
            Set c = .Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
            On Error GoTo 0
            If c Is Nothing Then
                .Range("M9").Value = .Range("M9").Value + 1
                currentbatrow = .Range("M9")
                On Error Resume Next
                Set c = .Range("E" & currentbatrow & ":" & "R" & currentbatrow).Find(what:="", LookIn:=xlValues, SearchOrder:=xlByRows)
                On Error GoTo 0
            End If

On Error Resume Next
            c.Value = .Range("C2")
            If c.Value = "Bowled" Or c.Value = "Caught" Or c.Value = "LBW" Or c.Value = "Stumped" Then Range("M9").Value = Range("M9").Value + 1
MsgBox c.Address(0, 0) & vbCr & c.Parent.Name & vbCr & c.Value
            If Not c Is Nothing Then
                c.Parent.Activate
                c.Activate
                End If
            End If
    End With
End Sub