VirtuallyUseful – Random Tech Babble

October 29, 2017

Blogtober Post 6 – Accessing data when you don’t know where it is, examples using INDIRECT and VBA

Filed under: Excel — Ray Olander @ 12:59 PM

In the Nimble Storage planning / script generator sheet I wrote, I whoever was using the sheet to modify the areas throughout the workbook that would be cleared when the “nuke it” button was pressed. I had defined all the data ranges that I had entered by default, but I allowed a lot of customization to all the sheets. For example,there were a number of data validation tables that I allowed the user to extend beyond my default values and I wanted the “nuke it” script to clear their new data as well. There have been other occasions where a cell or range reference might not be known when the sheet was opened, but needed to be used at some point during use. This capability has saved my bacon a couple times so I thought it would be a good addition to the Blogtober postings.

Using Excel formulas, we have the INDIRECT(ref_text,[style]) formula, where [style] is optional and is used as TRUE (A1) or FALSE (R1C1). The default is A1, which is almost all I use, so I never bother defining that portion of the formula.

The REALLY REALLY cool thing about INDIRECT is that it allows you to concatenate values into either a A1 or R1C1 reference and then use that.

For example, if I wanted to see what information was stored in a cell, but the column and row values were randomly generated (perhaps when generated random sample data from a large dataset), I can use the INDIRECT formula in place of the [reference] value in CELL(info_type,[reference]) as such:

 

Or, if I wanted to find out some information based on a range instead of a cell, I could concatenate two values into a range format as such:

 

In the planning sheet I referred to, the DATATOCLEAR sheet had the custom ranges the user could identify for the VBA script to clear

 

Here’s the code I used for clearing the TABLES sheet (NOTE: I used the “!” symbol to allow me to keep a range definition, but just skip it):

TABLES.Activate
For counter = 3 To 50 ' Read all 50 cells and act on any that are populated
    If DATATOCLEAR.Range("A" & counter).Value = "" Then
        GoTo JUMPSHIP_TABLES
    ElseIf Left(DATATOCLEAR.Range("A" & counter).Value, 1) = "!" Then
        GoTo JUMPSHIP_TABLES
    End If
    TABLES.Range(DATATOCLEAR.Range("A" & counter)).ClearContents
JUMPSHIP_TABLES:
Next
TABLES.Range("A3").Activate

 

For each cell that wasn’t blank:

If DATATOCLEAR.Range("A" & counter).Value = "" Then

Clear the data ranges specified in the DATATOCLEAR “A” column

TABLES.Range(DATATOCLEAR.Range("A" & counter)).ClearContents

 

Although this is pretty straight forward, the nice thing is that I was able to define the range as a string to be read, not as a value in the formula. Not exactly an INDIRECT equivalent since the range wasn’t concatenated, but the same in the sense that a string was used for A1 values.

 

 

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: