Hi all
This is our first newsletter coming from our new Website and contains a lot of information that will not always be included. I apologise for it being a bit long-winded!
Well the big news for Ozgrid.com this month is the transfer of our Website to a new location. After being hosted by Homestead Personal for the last 12 months we were told that we could no longer reside there as a commercial site. The reason being that Homestead Personal is now for personal Websites only. Although the transfer was a bit earlier than expected it was certainly anticipated. The good news is that Homestead.com now offer a Professional service for commercial sites.
We have completed the move now and while you can still access our Homepage via the usual http://www.ozgrid.com all other pages now have a new address! You can get there easily via the Homepage or by going to one of the old addresses. Once you are there you can simply click the link, or wait 8 seconds and you will be taken to the correct page automatically. We have been told that these redirection pages can stay until September, so please update all links before then.
Due to the moving of the Website and circumstances beyond our control, we unfortunately lost our full list of previous subscribers. I have manually gone through our address book and added people I believe have subscribed in the past. If you receive this newsletter and do not wish to, please send a reply with the exact words "Remove me" in the subject line.
New PagesThe other new regarding the Website is that we took the opportunity to add another 7 WebPages to the site and do some cosmetic changes. I certainly won't confess to being a expert in the area of HTML and other associated areas of Websites, so if anybody has any problems at all with these new pages (or any others) please do not hesitate to contact me.
UDF'sThis page can be found on the Site menu by following "UDFs". Once there you will see some text on User Defined Functions (Custom Functions). There are four examples of UDF's and the code for each. As with all code and examples on the site feel free to copy and paste them straight in a Module and use as are, or modify to suit. For anyone not sure what UDF's are, they are Functions that are written in Visual Basic for Applications (VBA) to suit your exact needs. Once written they can be used on any Worksheet within the Workbook just as you would with one of Excels many built- in Functions, eg; SUM, AVERAGE etc.
NewsfeederThis webpage is not associated with Excel in any way. It is a table of links that is managed by Add Me.com and will be updated automatically by them on a continuos basis. Add me.com is a leading resource for website and e-business promotion on the Internet. If you have a small e-business then I recommend a visit!
Free Stuff!!This new page is where I have posted four zipped Excel Workbooks examples for download. They give working examples on some of Excels Functions and features. I will certainly be adding to this page as time goes on.
Ok, now we have got all that out of the way, let's get look at the reason you probably subscribed to the newsletter-Excel!
CIF'sI thought that this month we would take a look at a way anyone can make there own UDF without any VBA knowledge at all! The term UDF is not technically correct in this context, so we will refer to these as CIF (Custom Interface Functions). These certainly don't have the same extent of flexibility as UDF's, but can still be very handy so let's look at what I'm talking about!We are probably all aware that we can name ranges in Excel, which makes our formulas a lot easier to read. But we can also name formulas which can make our formulas much easier to use and add more flexibility.
Before we jump in and use an example let's use one that seems to be a common request. VLOOKUP is possibly one of Excels most popular functions and with good reason. It searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. it's Syntax is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
This is all fine if it's the leftmost column you need to look in and the value you want returned is to the right of this column. But let's face it, our data is not always conveniently been set up this way. I often get requests from Excel users along the lines of:
What do I do if the column I want to search in could be in any position of a my table and the value is to the left of that column, not the right?
Let's say that we need to find out the Rate of "Mary L" (or any other name) like in the table in the attached Workbook.
The example I will use includes a variety of functions and nested functions, all of which could be of use on their own. It is a bit long winded but stick with me!
We first make our entire table a named range. Let's call it "Table"
- Let's now use the ADDRESS and MATCH functions to find the cell address of "Name". Remember it may be any position within our table. In this case it is in cell B1. We use:
=ADDRESS(1,MATCH("Name",$1:$1,0))
This will return the text $B$1 - Lets now make this Text cell addresses into a Text range address.
=ADDRESS(1,MATCH("Name",$1:$1,0)) & ":" & ADDRESS(500,MATCH("Name",$1:$1,0))
This will return the text $B$1:$B$500
We have used 500 in this case just to be certain we include all possible rows in each Text range. The & ":" & is used to place in our list separator. - Let's now use this Text range to find out the row number of "Mary L". As you may be aware, for Excel to recognise Text as a range or address we need to place it within the INDIRECT function. So we use:=MATCH("Mary L",INDIRECT(ADDRESS(1,MATCH("Name",$1:$1,0)) & ":" & ADDRESS(500,MATCH("Name",$1:$1,0))),0)
This returns 5 the row number of "Mary L" - Now we need to find the Column number of the heading "Range" which in this case is 1, as the heading "Range" is in cell A1. So we use:
=MATCH("Rate",$1:$1,0)
This returns the number 1 - Now we have all the values we need to enable us to find the name "Mary L" somewhere under the column heading "Name". Now we need to return the value from the same row, but under the column heading "Rate". To achieve this we need to nest our above formulas as the Row_num and Column_num arguments in the INDEX function. So our end formula is:=INDEX(Table,MATCH("Mary L",INDIRECT(ADDRESS(1,MATCH("Name",$1:$1,0)) & ":" & ADDRESS(500,MATCH("Name",$1:$1,0))),0),MATCH("Rate",$1:$1,0))
Are you are still with me? if so, we certainly DO NOT want to have to write this mega formula each time we need to find out the pay rate of a named person. This is where we make our CIF! This is how we do it.
- Select the cell that has the mega formula, push F2 and highlight the formula and copy it, then push Enter.
- Select cell H2 and push Ctrl+F3, to show the Define name dialog box.
- Delete all text within the Refers to box and make sure your mouse insertion point is still in the box. Push Ctrl+V to paste in the formula.
Replace the text "Mary L" in the formula with the text "H1" (no quotations). In the Names in workbook box type the name "FindRate" (no quotations). Click Add then click OK.
- Change the formula:
=ADDRESS(1,MATCH("Name",$1:$1,0)) & ":" & ADDRESS(500,MATCH("Name",$1:$1,0))
TO
=INDIRECT(ADDRESS(1,MATCH("Name",$1:$1,0)) & ":" & ADDRESS(500,MATCH("Name",$1:$1,0))) - Copy the formula in the same way as we did above in step 2.
- Select any cell and go to Data>Validation select "List" from the allow box.
Delete any text in the "Source" box and make sure your mouse insertion point is still in the box. Push Ctrl+V to paste in the formula. - Make sure the "In-cell dropdown" and the "Ignore blank" checkboxes are checked and click OK.
Now all we need to do is select the cell directly below the cell we just put our Validation list in and type:
=FindRateThis will always return the rate of the person in the cell directly above.
I realise there are a few other ways we could use to find the rate of a specified name (DGET springs to mind) but I have used this method so you can see a few other formulas. I also used it so I could pass on a few other tips along the way and of course to demonstrate a CIF.
OzGrid SpreadsheetsAt we OzGrid can fully automate your current spreadsheet or create a new one for you! This is just one of the many services we offer.
VBAI guess we should look at a bit of VBA for all you coders out there!
It never ceases to amaze me how many times I keep seeing a Loop being used to delete blank rows. Using a Loop for this is totally unnecessary 99.9% of the time. For those of you who are not aware of my feelings on Loops, they are that Loops should only be used as a last resort and even then only once the range to Loop through has been narrowed down. I find Loops far too slow for most situations. Excel has so many brilliant built in features that can be used in place of Loops in a lot of cases. The Find Method for instances can be more than a 10000 times faster! To show you just one instance, try this:
Place the text "Find Me" into cell IV65336 and run this code:
Sub NoLoop()
Cells.Find(What:="Find Me", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
Now if you have at least 5 minutes to spare, try this code:
Sub WithLoop()
Dim rCell As Range
For Each rCell In Cells
If rCell.Value = "Find Me" Then
rCell.Activate
Exit For
End If
Next rCell
End Sub
To stop the Loop, push Ctrl+Break or Esc. Now that has to at least make you try alternatives for Loops!
Anyway, back to the blank row problem. This first method I will show is certainly not the best way, but It will show you how the Advanced Filter can be used.
Sub BlanksFromList()
'Check if there are blanks
If WorksheetFunction.CountBlank _
(Range("A1", Range("A65536").End(xlUp))) = 0 Then Exit Sub
'Insert blank rows for criteria
Rows("1:3").EntireRow.Insert
'Place heading in criteria heading
Range("A1").Value = Range("A4").Value
'Use = as the criteria (blank cells)
Range("A2").Value = "="
'Do the Advance filter for blanks only
Range("A4", Range("A65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:A2"), Unique:=False
'Delete all blank rows
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Take off filter and remove criteria rows
ActiveSheet.ShowAllData
Rows("1:2").EntireRow.Delete
End Sub
This next method is possibly the best method to remove blank rows.
Sub BlanksFromList2()
On Error Resume Next
Range("A1", Range("A65536").End(xlUp)).SpecialCells _
(xlCellTypeBlanks).EntireRow.Delete
End Sub
Or.......
Sub BlanksFromList2()
On Error Resume Next
ActiveSheet.UsedRange.Columns(256).FormulaR1C1 = _
"=IF(COUNTBLANK(RC[-255]:RC[-1])=255,NA(),1)"
Columns(256).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Columns(256).Clear
End Sub
This one simply put the formula: =IF(COUNTBLANK(A1:IU1)=255,NA(),1) in the used range of the sheet in Column IV. If the entire row is blank it will return the #NA! Error. I then use the SpecialCells to delete the entire row for all error cells in column IV
At the end of the day I guess I am saying that there are many many ways to remove blank rows and a Loop is about the bottom of my list of choices. If I did need to use a Loop I would at least narrow the field down in some way using SpecialCells, AutoFilter, AdvancedFilter, Find etc.
Well that's it for this month, hope you all get something from this.
Kind regards
Zipped Workbook to go with Issue 3.