Solved

Test for a range that evaluates to nothing

Posted on 2014-10-19
8
74 Views
Last Modified: 2014-10-19
Hi

I am using excel in office 2013.   The following code fails when there are no constants in the selected range.  It works fine if the range does not evaluate to nothing.

I need the following logic

If rStoreConstants  <> nothing then do something


Dim RStoreConstants As Range
Set RStoreConstants = TheWorkSheet.Range(Cells(FirstRow, 1), Cells(Lastrow, LastColumn)).SpecialCells(xlCellTypeConstants)

Open in new window

0
Comment
Question by:peispud
[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
  • 3
  • 2
8 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40390701
Hi,

You will need to ignore errors (with an On Error Resume Next statement above your existing code), but you may use this:

If Not (RStoreConstants Is Nothing) Then
rem Do Something here
End If
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40390702
If Not rStoreConstants Is Nothing Then
0
 

Author Comment

by:peispud
ID: 40390708
I've used this code.  A pop up comes up saying "No cells were found."

If Not TheWorkSheet.Range(Cells(FirstRow, 1), Cells(Lastrow, LastColumn)).SpecialCells(xlCellTypeConstants) Is Nothing Then
        Set RStoreConstants = TheWorkSheet.Range(Cells(FirstRow, 1), Cells(Lastrow, LastColumn)).SpecialCells(xlCellTypeConstants)
End If
    

Open in new window

0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 47

Expert Comment

by:Martin Liss
ID: 40390715
Are the cell values in the range the result of formulas?
0
 

Author Comment

by:peispud
ID: 40390719
mixed.   There are always formulas in the selected range.   There may be constants.
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 40390720
This code performs as you expected (because of the On Error Resume Next statement, as I mentioned above):

  Dim LastColumn                                        As Integer
  Dim FirstRow                                          As Long
  Dim LastRow                                           As Long
  Dim RStoreConstants                                   As Range
  Dim TheWorkSheet                                      As Worksheet
  
  LastColumn = 20
  FirstRow = 1&
  LastRow = 10&
  
  Set TheWorkSheet = ActiveSheet
  Set RStoreConstants = Nothing
  
  On Error Resume Next
  Set RStoreConstants = TheWorkSheet.Range(Cells(FirstRow, 1), Cells(LastRow, LastColumn)).SpecialCells(xlCellTypeConstants)
  On Error GoTo 0
  
  If Not (RStoreConstants Is Nothing) Then
     Rem Do Something
  End If

Open in new window

0
 

Author Closing Comment

by:peispud
ID: 40390727
Thank you all.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40390730
You're welcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

734 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