OzGrid Excel and VBA Newsletter

Search Tips

Newsletter Index

Search Tips

What's New at OzGrid |  Excel Tips and Tricks | Excel VBA Tips and Tricks

What's New at OzGrid

As we approach the festive season we are madly trying to get things to the stage where we can have a few days off to enjoy the holidays and the hot weather.

We received some great news from Amazon.com this month. Our book, Excel Hacks - 100 Industrial Strength Tips and Tools has made the Editors No. 1 pick for theAmazon Computers & Internet Section. We are very pleased, and we have already seen a positive effect with increased book sales

Also this month, yesterday in fact, we received a record number of visitors to our web-site. We think this is a great achievement so close to Christmas.

We would like to wish all our newsletter readers and visitors to our website a very happy, safe and prosperous Christmas and New Year.

Excel Tips and Tricks

I don't think it's any secret that I'm a pretty big fan ofDynamic Named Ranges

They are ideal to be used in a vast array of different situations. This month I thought I would show you 2 Dynamic Named Ranges with a twist.

BASED OFF LONGEST COLUMN OF DATA

A Dynamic Named Range formula for a table of data that occupies say A1:D<whatever> may look like below;

Name: MyTable

Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A),4)

Where: COUNTA($A:$A) is being used to determine how many rows to expand down and 4 is the number of columns to include Starting from $A$1.

This of course will work fine if Column A is the column with the most amount of data and there are no blank cells. But what if you have no idea which of the columns (A:D) will have the most data and if there will be blanks?

Let's first address the blank cells issue. On the page, the link above points to, you will see that numbers 3 and 4 address the issue of the possibility that there might be blank cells between data. That is;

3**Expand Down to The Last Numeric Entry**

In the Refers to box type: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)

If you expect a number larger than 1E+306 (a one with 306 zeros) then change this to a larger number.

4**Expand Down to The Last Text Entry**

In the Refers to box type: =OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

As you can see though, this does not account for the possibility of text and numbers in the same column. However, we should always have column headings in a table and these should be text. So to find out the last used cell in Column A we could use;=MAX(MATCH(1E+306,$A:$A,1),MATCH("*",$A:$A,-1))

This formula requires that there is BOTH text and numbers in Column A. So, while the Column heading will ensure we have text, we need to ensure there is at least 1 number. To do this, insert a new row at Row 1 (select Row 1 and go to Insert>Rows) and then in A1:D1 enter the number 0. You can now hide this row (select Row 1 and go to Format>Row>Hide). To use this to determine which of the Columns (A:D) has the highest row number we would go to Insert>Name>Define and use these names with the formula below as the result for their "Refers to".ColA=MAX(MATCH(1E+306,$A:$A,1),MATCH("*",$A:$A,-1))ColB=MAX(MATCH(1E+306,$B:$B,1),MATCH("*",$B:$B,-1))ColC=MAX(MATCH(1E+306,$C:$C,1),MATCH("*",$C:$C,-1))ColD=MAX(MATCH(1E+306,$D:$D,1),MATCH("*",$D:$D,-1))

Now we can create one more Name (MaxCol) and have this one return the maximum number of the Names above. This would simply be;MaxCol=MAX(ColA,ColB,ColC,ColD)

Now we have this done we can replace our original Dynamic Named Range (MyRange) "Refers to" range with

=OFFSET($A$1,0,0,MaxCol,4)

This will ensure that are named range always expands down to the last used cell in ColumnsA:D and it doesn't matter if there are blank cells and/or a mix of text and numeric entries.

DYNAMIC RANGE WITHIN A RANGE.

ThisDynamic Named Range is ideal for a long text list that is sorted A:Z. We can also make use of the "List" feature of Validation to make it even better. For the purpose of this example I will assume to list is in Column A and has been sorted A:Z

Select B1 and go to Data>Validation choose "List" then in the "Source" type; A,B,C,D....Z

Now go to Insert>Name>Define and use the Name: AlphaList and for the "Refers to" use;=OFFSET(INDIRECT(ADDRESS(MATCH($B$1 & "*",$A:$A,0)+1,1)),0,0,COUNTIF($A:$A,$B$1 & "*"),1)

Now, whenever you select a letter from the list in B1 the Dynamic Named Range (AlphaList) will refers to only the group of cells that Start with the letter chosen. In other words, if the list was names (and of course must be sorted A:Z) and you chose the letter "M" from B1, the Dynamic Named Range AlphaList will only refer to the names that Start with "M".

The uses of this are only limited to your imagination.

Build a Stock Trading Model Using Microsoft Excel

Excel VBA Tips and Tricks

Let's this month take a look at one of the most commonly asked questions. That is, how can I make something happen based on the content of a specific cell. For example, if A1 gets above 100 I would like to have a specified macro run.

This would require the use of one of the Worksheet events, in this case the Change event. As with all Sheet events, the code must reside in the Private Module of the Sheet Object. The quickest way to get to this module is to right click on the sheet name tab and select "View Code". Then, in here, you would place some code like shown below;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" And Target > 100 Then
        Run "MyMacro"
    End If
End Sub

As you can see, Target is a Range Object that represents the cell that is being change. All we need to do is compare the Target address with that of A1. There is however one problem with this code and that is, some text being entered into A1 could cause the IF Statement to return True and hence run "MyMacro". This can easily be over-come with the use of the IsNumeric Function as shown below;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If IsNumeric(Target) Then
            If Target > 100 Then
                Run "MyMacro"
            End If
        End If
    End If
End Sub

This will now ensure the macro "MyMacro" only runs when it should.

Another common question, along the same lines, is how can I have a date stamp entered in a corresponding cell. Let's assume you wish to monitor the range A1:A100 and anytime a new entry is made in one of these cells, a date stamp is entered on the corresponding row in Column D. The code to do this would be like shown below;

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A100")) _
        Is Nothing Then Exit Sub

    With Target(1, 4)
        .Value = Date
        .EntireColumn.AutoFit
    End With
End Sub

In this case, as we need to monitor a range of cells and not one, we have use the Intersect Method to find out if the Target cell is within the range A1:A100. If it is not, we have told Excel to Exit Sub (do nothing more). The Intersect Method returns a Range Object only if the ranges being used (within the Intersect Method) intersect. If the ranges do not intersect, Nothing is returned. Nothing is often used to to disassociate an object variable from an actual object. I.eSet MyRange = Nothing

Let's now use some change event code to keep track of all data changes being made to a sheet. This can be handy if you have a lot of different people inputting onto the same Worksheet.

The first thing to do is add a sheet we can use to keep track of all the changes. Then hide this sheet, either via Format>Sheet>Hide, or make it XlVeryHidden via VBA code. E.g

Sheet1.Visible = xlSheetVeryHidden

Note the use of thesheets Code Name

Now, in the sheet you wish to monitor place this code;

Dim vOldVal 'Must be at top of module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBold As Boolean
    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheet1
            .Cells(1, 1) = "CELL CHANGED"
            .Cells(65536, 1).End(xlUp)(2, 1) = Target.Address
            .Cells(1, 2) = "OLD VALUE"
            .Cells(65536, 2).End(xlUp)(2, 1) = vOldVal
        With .Cells(1, 3)
            .Value = "NEW VALUE"
            .ClearComments
            .AddComment.Text Text:= _
            "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
            "Bold values are the results of formulas"
        End With
        With .Cells(65536, 3).End(xlUp)(2, 1)
            .Value = Target
            .Font.Bold = bBold
        End With
            .Cells(1, 4) = "TIME OF CHANGE"
            .Cells(65536, 4).End(xlUp)(2, 1) = Time
            .Cells(1, 5) = "DATE OF CHANGE"
            .Cells(65536, 5).End(xlUp)(2, 1) = Date
            .Cells.Columns.AutoFit
        End With
vOldVal = vbNullString
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    vOldVal = Target
End Sub

Until next month, keep Excelling!

ADVERTISEMENTS

Artificial neural network software for stock markets!

EXCEL TEMPLATES SPECIALS

DATABASE SOFTWARE

MAIN SOFTWARE CATEGORIES