OzGrid's Excel Newsletter

Contained at the bottom of eachnewsletter is Helpful Information on how to get the most from ournewsletters.

Should you wish to no longer receive ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere.

Youare more than welcome to pass this on to as many people as you wish.

SPECIAL! Get all the OzGrid Add-ins, together and Save! $54.95

INDEX

What's New at OzGrid.com| New/Updated Pages | Microsoft Excel tips

Microsoft Excel VBA tips | Helpful Information

Check Out These Great Links:

This month we have kindly been offered a 25% discount for Investor�s Toolkit.com.au . If you are into shares or property these guys are a must see!

• Template Zone
• Excel Templates
• OzGrid Excel Forum
• Id and Barcodes

• OzGrid Partner Program

• VBA Loops (Join the text of multiple cells)
• Downloadable Training Specials (New bundled savings added)
   

What's new at OzGrid.com [Top]

Hi all

The BIG news for OzGrid this month is the launch of our new Excel Question and Answer Forum We already have 360 registered members and this looks set to increase rapidly. Most questions are being answered with the hour and often sooner!  At present there is no requirement toregister, but it is likely to become this way in a month or so. Registration will cost nothing and only requires a Username, Password and email address.  I'm sure that if you use Excel at all you will find this forum invaluable.

Our Downloadable Training is proving to be very popular and economical method of training. These full courses are ready for immediate Buy/Download from secure servers and are VERY good value as there are no licence agreements. This means that one purchase can be made available to an entire workforce. We have also bundled up these courses for even bigger savings. Combine these with our new Excel Question and Answer Forum and you will be an Excel guru in no time.

Onto the good stuff!last month we looked at Part 1 of 2 on Validation and used it to restrict entires between nominated values and a list of allowed entries. This month we will take this one step further and look at how we can even use formulas as our criteria.

When we enter data into a validated cell, Excel checks to see whether the entry returns TRUE, meaning it's allowed, or FALSE, meaning it's not allowed. It is this simple Boolean (TRUE or FALSE) value that we also use when applying a formula to the Validation. Our formula MUST return either True or False. Let's look at a simple way in which we can Validate a cell against a person entering a number into a cell that would result in it, and all the cells above it, summing to a value that is too high. Let's make that value 100

1. Select cells A1:A10 on any sheet.
2. Go to Data>Validation and select Custom from the Allow: box
3. Enter: =SUM($A$1:$A$10)<101 in theFormula: box.
4. Type an Input message and/orError alert if you wish.
5. Click Ok.

Now start entering numbers in the range A1:A10. Note that if a number you enter will mean the SUM value ofA1:A100 is greater than 100, the Validation will prevent it from being entered. The reason is very simple, while the sum of our numbers is less than 101 our formula, or expression (=SUM($A$1:$A$10)<101) results in TRUE so all is ok. But, as soon as a number being entered would result in our expression evaluating to FALSE, Excel prevents the entry. You can visibly see this by simply entering the formula =SUM($A$1:$A$10)<101 into any cell outside of the range A1:A100. In fact, this is a great way to find out the needed formula to use when coming up with your own uses for this. Just keep in mind the formula must evaluate to True or False and nothing else.

Let's try one more example using the Custom option. This time we want the user to enter data into a list, but only when the cell directly above contains data. In other words we do not want blank cells in our list. before we start, clear the range B1:B10 of all data.

1. Select cells B2:B10 starting from B2. This makes B2 the active cell.
2. Go to Data>Validation and select Custom from the Allow: box
3. Enter: =LEN(B1)<>0 in the Formula: box.
4. Uncheck "Ignore Blanks"
5. On the Error alert page, type "Please fill the cell above first"
6. Click Ok

Now try and enter any data into any cell and we are told "Please fill the cell above first" We must fill in cell B1, perhaps with any heading. We can now enter into B2, but if you try to skip a cell you will be stopped.

Let's finish off this months Excel tips with some work with Text Strings. In the first example we see how to extract specific words an/or characters from them. Let's assume we have just been handed a huge list of names that have both the first name and the last in the same cell. Our task is to separate these names into first name and surname. Our names all reside in Column "A". Use this formula, entered in B1, to extract the first name.

=LEFT(A1,FIND(" ",A1)-1)

Now in cell C1 enter

=SUBSTITUTE(A1,B1 & " ","")

Now copy select cells B1:C1, then double click the Fill handle (small black square bottom right of selection), this will copy our formulas down as far as the first blank cell in Column "A". Now select Columns B:C and copy, then Edit>Paste special - Values over the top of themselves. Before you do though, rather than me explaining just how the formulas work, you guys can see this for yourself.

1. Select cell B1
2. Click the = sign immediately to the left of the Formula bar, it will be the fx symbol in Excel XP.
3. Now step through the formula by selecting the function name LEFT and FIND in the formula bar.

XP now has some new features that allow us to 'de-bug' formulas in cells in a similar way that we can do in VBA. See "Find and correct errors in formulas" in the help for details.

Now, while this formula works there is another, perhaps easier way. It is called Text to columns... and it is often used to separate text (from text files) into different columns. The feature can be found under Data on the Worksheet Menu Bar and is very easy to use and very flexible.

There are many many uses for Excel's Text functions and their use normally involves nesting. We have covered nesting in past issues, but if you have forgotten or were not subscribed they are here andhere

One interesting request that was posted on our newExcel Forum was how to have a cell, that was summing up hours, return the result in the Format "x days and x hours and x minutes" I do not think this can be done with formatting alone (now there's a challenge) but it can be done with a formula. Let's enter a large number of hours and minutes into cell A1, say75:45:00, that is Custom formatted as [h]:mm. Now in cell B1 put this formula

=INT(A1) &" Days " & INT(MOD(A1,INT(A1))*24) & " Hours and " & MINUTE(A1) & " Minutes"

You should get the result of: 31 Days 6 Hours and 45 Minutes

Theonly problem with this result is we cannot use it if any further time calculations. We can however work around this by using our original time cell (A1) for any calculations. Those that are familiar with how Excel sees Dates and Times will see how the result is obtained. For those that are not you can proceed to Excel Date and Times

OzGrid Excel Plus | Formula Ref Changer | Sheet Index Creator | Duplication Manager

Would you like over1200 VBA examples?

Microsoft Excel VBA tips [Top]

This may come as a surprise to those that know of my hesitations in using loops, but this month I thought I would show you all that I do not avoid them entirely. I have said it before and will say it again, loops are fine for looping through a Collection (group of similar Objects), but when that Collection is Cells and there are many of them and there is a need for a few IF statements, you should often look for one of Excels built in features to do the work.

Let's look at one of Excels fastest Loops, the For Each loop. A For Each loop will repeat a line, or lines, of code as many times as there are Objects in a Collection.

Dim rCell as Range

For Each rCell In Range("A1:A100")
    'CODE TO RUN ON EACH CELL
Next rCell

This is the classic syntax for a For Each loop.  Note that we have dimensioned a variable as a Range Object. It is mandatory that we use a variable which is of the same Object type as the Collection we wish to loop through. rCell is the Object type and Range("A1:A100") is the Collection. Perhaps the best part of a For Each loop is that we do not need to specifically tell it how many times to loop, it will already know by the Collection we supply. In this case it is simple for even us to see that the code will loop 100 times. There are many times though that we have no idea how many Objects there will be in our Collection!

One of the most frequently used cases for a For Each loop can be when we need to do something with all the Worksheets in a Workbook, but have no idea just how many Worksheets there are.

Sub NameA1OnEachWSheet()
Dim wsheet As Worksheet

    For Each wsheet In Worksheets
        wsheet.Range("A1").Name = wsheet.Name
    Next wsheet

End Sub

This example loops through all Worksheets in the active Workbook and names cell A1 of each Worksheet the same name as the Worksheet itself. Don't confuse Sheets with Worksheets though! Sheets will return aSheets Collection that represents all (Worksheets and Chart Sheets etc) the sheets in the active workbook, while the Worksheets Collection only represents Worksheets.

Some of you maybe surprised to learn that Names is also a Collection, it is a Collection of Name Objects. Let us use a For Each loop to loop through all names in the active Workbook and list some details about them.

Sub DetailsOfNames()
Dim nName As Name
Dim strRange As String

Range("A1") = "Name"
Range("B1") = "Refers to Range"
Range("C1") = "Resides on Sheet"
Range("D1") = "Is Visible"


    For Each nName In Names
        With Range("A65536").End(xlUp)
            strRange = nName.RefersTo
                .Cells(2, 1) = nName.Name
                .Cells(2, 2) = "'" & strRange
                .Cells(2, 3) = "'" & Left(strRange, _
                 InStr(1, strRange, "$") - 1)
                .Cells(2, 4) = nName.Visible
        End With
    Next nName
End Sub

You of course must have some named ranges in the Workbook for this to work. We also had the single apostrophe to the front of the "Refers To Range" and the "Reside on Sheet" or else Excel will think we are entering a formula!

Let's now use a Loop to loop through all Excel Workbooks that are in a specified folder, open them, do some stuff, then close them. This sort of code is very handy when you have many Workbooks that need the same code run on them, all you need to do is place them all in the same folder.

Sub RunCodeOnAllXLSFiles()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

    Set wbCodeBook = ThisWorkbook

        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\MyDocuments\TestResults"
            .FileType = msoFileTypeExcelWorkbooks

                If .Execute > 0 Then 'Workbooks in folder
                    For i = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                         Set wbResults = Workbooks.Open(.FoundFiles(i))

                            'Do Your Code Here

                    Next i
                End If
        End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

As you can see we have this time used a For loop to open each Workbook in the specified folder. This sort of code is very handy to have as all you need to do is change the path and insert the needed code into the loop. It can save hours, if not days of work!

Youare more than welcome to pass this on to as many people as you wish.

SPECIAL! Get all the OzGrid Add-ins, together and Save! $54.95