OzGrid Excel and VBA Newsletter AUGUST 2007

Excel Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

Here are some excellent ways to enhance your Excel skills and/or VBA skills with training and/or software

EXCEL TIPS AND TRICKS

DEPENDENT LISTS WITH LOOKUP FUNCTIONS

We are going to look at how to use just ONEVlookup Function to look in any number of named tables/ranges. To make it user friendly we are going to use a Data Validation trick with lists and have the 2 (or more) lists linked. That is, choosing an item from our 1st list will result in a corresponding list showing the second. To see how to do this, see Dependent Data Valiation Lists and even download thebasic example.

1 VLOOKUP FOR MANY TABLES

Download Example

The principle we are going to use is the same as we used to link the 2 validation lists in thebasic download example. That is, we use the Indirect Function which will allow Excel to see the content of any cell as either a range address, or, as in our case, a named range.

Our end Vlookup Function, based on the download example above, will be this;
=VLOOKUP($B$2,INDIRECT(SUBSTITUTE($A$2&2," ","_")),$C$2,FALSE)

$B$2 (Lookup_value) is the value we are going to be looking for.

INDIRECT(SUBSTITUTE($A$2&2," ","_")) (Table_array) is the named table we are going to look for $B$2 in and in the left most column of that table. Note the use of the SUBSTITUTE Function which substitutes any spaces with the underscore. This is because named ranges can never have spaces in their names. Also note the use of $A$2&2 and not simply $A$2! This is because we have already named the lists we use in the Data Validation list the same as whatever is chosen from A2. So, when we name our tables, we use the same name as their lists, but add a 2 on the end. For example, our table for Cities has been named "Cities2".

$C$2 (Col_index_num) contains a Data Validation list with the numbers 2 and 3 (only 3 columns in example). Our Vlookup will offset (right) that many columns from the left most column in the named table it looks in.

FALSE (Range_lookup) tells Vlookup we want an exact match (not case sensitive).

#STOP THE N/A!

As you change items in the Data Validation lists you will get #N/A! until a existing item or the correct named table is chosen. This can be rectified in a number of way, some less efficient that others though. SeeStop #N/A! in Lookups

EXCEL VBA TIPS AND TRICKS

WORKING WITH SHAPES

Shapes are those from the Drawing toolbar or the Forms toolbar.

LISTING SHAPE PROPERTIES OF ACTIVE WORKSHEET

The code below will create a new Worksheet where the Shape Properties are listed under their appropriate heading.

Sub GetShapeProperties()    Dim sShapes As Shape, lLoop As Long    Dim wsStart As Worksheet, WsNew As Worksheet        ''''''''''''''''''''''''''''''''''''''''''''''''    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''    ''''''''''Dave Hawley www.ozgrid.com''''''''''''    ''''''''''''''''''''''''''''''''''''''''''''''''        Set wsStart = ActiveSheet    Set WsNew = Sheets.Add         'Add headings for our lists. Expand as needed    WsNew.Range("A1:F1") = _     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top")              'Loop through all shapes on active sheet    For Each sShapes In wsStart.Shapes        'Increment Variable lLoop for row numbers        lLoop = lLoop + 1        With sShapes            'Add shape properties            WsNew.Cells(lLoop + 1, 1) = .Name            WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.Name            WsNew.Cells(lLoop + 1, 3) = .Height            WsNew.Cells(lLoop + 1, 4) = .Width            WsNew.Cells(lLoop + 1, 5) = .Left            WsNew.Cells(lLoop + 1, 6) = .Top            'Follow the same pattern for more        End With    Next sShapes         'AutoFit Columns.    WsNew.Columns.AutoFitEnd Sub

LISTING SHAPE PROPERTIES OF ALL WORKSHEETS

The code below will create a new Worksheet where the Shape Properties from all Worksheets are listed under their appropriate heading.

Sub GetShapePropertiesAllWs()    Dim sShapes As Shape, lLoop As Long    Dim WsNew As Worksheet    Dim wsLoop As Worksheet        ''''''''''''''''''''''''''''''''''''''''''''''''    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''    ''''''''''Dave Hawley www.ozgrid.com''''''''''''    ''''''''''''''''''''''''''''''''''''''''''''''''        Set WsNew = Sheets.Add         'Add headings for our lists. Expand as needed    WsNew.Range("A1:G1") = _     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top", "Sheet Name")         'Loop through all Worksheet    For Each wsLoop In Worksheets        'Loop through all shapes on Worksheet        For Each sShapes In wsLoop.Shapes            'Increment Variable lLoop for row numbers            lLoop = lLoop + 1            With sShapes                'Add shape properties                WsNew.Cells(lLoop + 1, 1) = .name                WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.name                WsNew.Cells(lLoop + 1, 3) = .Height                WsNew.Cells(lLoop + 1, 4) = .Width                WsNew.Cells(lLoop + 1, 5) = .Left                WsNew.Cells(lLoop + 1, 6) = .Top                'Follow the same pattern for more                WsNew.Cells(lLoop + 1, 7) = wsLoop.name            End With        Next sShapes    Next wsLoop         'AutoFit Columns.    WsNew.Columns.AutoFitEnd Sub

LISTING SHAPE PROPERTIES OF SOME WORKSHEETS

The code below will create a new Worksheet where the Shape Properties from chosen Worksheets (those NOT named inSelect Case) are listed under their appropriate heading.

Sub GetShapePropertiesSomeWs()    Dim sShapes As Shape, lLoop As Long    Dim WsNew As Worksheet    Dim wsLoop As Worksheet        ''''''''''''''''''''''''''''''''''''''''''''''''    ''''''''''LIST PROPERTIES OF SHAPES'''''''''''''    ''''''''''Dave Hawley www.ozgrid.com''''''''''''    ''''''''''''''''''''''''''''''''''''''''''''''''        Set WsNew = Sheets.Add         'Add headings for our lists. Expand as needed    WsNew.Range("A1:G1") = _     Array("Shape Name", "Shape Type", "Height", "Width", "Left", "Top", "Sheet Name")         'Loop through all Worksheet    For Each wsLoop In Worksheets        Select Case UCase(wsLoop.name)           Case "SHEET5", "SHEET8" 'add sheet names to exclude              'Do nothing           Case Else            'Loop through all shapes on Worksheet            For Each sShapes In wsLoop.Shapes                'Increment Variable lLoop for row numbers                lLoop = lLoop + 1                With sShapes                    'Add shape properties                    WsNew.Cells(lLoop + 1, 1) = .name                    WsNew.Cells(lLoop + 1, 2) = .OLEFormat.Object.name                    WsNew.Cells(lLoop + 1, 3) = .Height                    WsNew.Cells(lLoop + 1, 4) = .Width                    WsNew.Cells(lLoop + 1, 5) = .Left                    WsNew.Cells(lLoop + 1, 6) = .Top                    'Follow the same pattern for more                    WsNew.Cells(lLoop + 1, 7) = wsLoop.name                End With            Next sShapes         End Select    Next wsLoop         'AutoFit Columns.    WsNew.Columns.AutoFitEnd Sub

Got any Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!

Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help