VBA Newbie - Runtime Error 91 using InStr

[Aragorn2001]

Hi all. I've gotten a lot of use out of MrExcel in the past, and I'm banging my head against a wall here. Complete VBA newbie (my last years of programming was 20+ years ago).

I'm getting the error "Object variable or With block variable not set" at the highlighted line.

Option Explicit         
Public cell As Range   


Sub superset_exercise()
Dim cellAbove, cellBelow, rangestart As Range
Dim Char, CharAbove, CharBelow As String
Dim strcell, strlengthA, strlengthB As Integer


If IsEmpty(cell) = True Then Exit Sub
[COLOR=#ff0000]strcell = InStr(1, cell, ")")             <<<runtime 91="" error<="" font="">
</runtime>[/COLOR]<runtime 91="" error<="" font="">

This sub is in the standard module, and I am trying to use this sub as a result of a worksheet change event. The Worksheet change event code is:

Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub ' bail if more than one cell selected
'Application.EnableEvents = False


Set cell = Target


Application.OnKey "{ENTER}", "superset_exercise()"
'Application.EnableEvents = True


End Sub

I don't understand why I'm getting this error -- I have set the public variable cell, I made both subroutines are public, and InStr should return a number value, not an object or string.

Would someone please help? This chunk is the beginning of a larger subroutine I am having problems with.</runtime>

 

[footoo]

Try following amendments :

Option Explicit
Public cell As Range
Sub superset_exercise()
Dim cellAbove As Range, cellBelow As Range, rangestart As Range
Dim Char As String, CharAbove As String, CharBelow As String
Dim strcell As Integer, strlengthA As Integer, strlengthB As Integer


If IsEmpty(cell) = True Then Exit Sub
strcell = InStr(1, cell, ")")
End Sub

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub ' bail if more than one cell selected
'Application.EnableEvents = False
Set cell = Target
Call superset_exercise
'Application.EnableEvents = True
End Sub

This might also help : https://www.houselogic.com/organize-maintain/diy-repair/repair-walls-give-rooms-fresh-face/

 

[Aragorn2001]

Hi footoo, thanks for replying. I made all the changes but still get the same error on the same line.

Thanks for the home and garden link, I'll probably need some new dry wall by the time I figure this out