Solved

Get rid of Run-Time Error '91'

Posted on 2014-12-22
3
220 Views
Last Modified: 2014-12-22
I keep receiving this error message, "Run-Time Error '91':Object variable or With block variable not set",  when ever I click on any cell outside of column A. It is very annoying and distracting. How can I get rid of this message?
Below is the code I am using

Should I get rid of the 'intersect' line?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("$A$12:$A$1500")) Then

    If Range("C24").Value = 0 Then
        Rows("24:27").EntireRow.Hidden = True
    Else
        Rows("24:27").EntireRow.Hidden = False
    End If
    
    If Range("C23").Value = 0 Then
        Rows("23").EntireRow.Hidden = True
    Else
        Rows("23").EntireRow.Hidden = False
    End If
    
    If Range("C31").Value = 0 And Range("C32").Value = 0 Then
            'both ranges are zero, hide rows 30-35
            Rows("30:37").EntireRow.Hidden = True
        ElseIf Range("C32").Value > 0 Then
            'range > 0, display rows 32-35
            Rows("32:37").EntireRow.Hidden = False
            Rows("30").EntireRow.Hidden = False
            'hide row 31
            Rows("31").EntireRow.Hidden = True
        ElseIf Range("C31").Value > 0 Then
            'range > 0, display row 31
            Rows("30:31").EntireRow.Hidden = False
            'hide rows 32-35
            Rows("32:35").EntireRow.Hidden = True
        Else
            'display rows 30-35
            Rows("30:35").EntireRow.Hidden = False
        End If
        
    If Range("C39").Value = 0 And Range("C40").Value = 0 Then
            Rows("38:45").EntireRow.Hidden = True
        ElseIf Range("C39").Value > 0 Then
            Rows("40:43").EntireRow.Hidden = True
            Rows("38:39").EntireRow.Hidden = False
        ElseIf Range("C40").Value > 0 Then
            Rows("40:43").EntireRow.Hidden = False
            Rows("39").EntireRow.Hidden = True
        Else
            Rows("38:45").EntireRow.Hidden = False
        End If
End If

End Sub

Open in new window

0
Comment
Question by:Boston617
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 40514026
Try testing the returned range for Nothing. This example uses a variable to capture the Range.
Dim rngIntersect As Range
Set rngIntersect = Intersect(Target, Range("$A$12:$A$1500"))

If Not rngIntersect Is Nothing Then

Open in new window

0
 
LVL 24

Expert Comment

by:mankowitz
ID: 40514037
I think the problem is that Intersect is returning a range, not a boolean. Try this instead

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("$A$12:$A$1500")) Is Nothing Then

Open in new window

0
 

Author Closing Comment

by:Boston617
ID: 40514042
that worked perfectly, thank you
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question