Sheets Vba
Creating a user defined function in Excel to find an intersection of a row header and column
The UNION operator is one of the operators most used Excel. The INTERSECT operator a little less. But what if they do not quite what you're looking for?
Let's step back a bit.
The operator UNION is simply the coma. If we take a function written as follows:
= SUM (A1: A10, C1: C10)
will refund the total of all cells in the range from A1 to A10 and all cells C1 to C10. In other words, it creates a range that is the union of the two ranges, then resume values.
The INTERSECT operator is just a space – for use, we just re-write the above function as
= SUM (A1: A10, C1: C10)
Is this change a comma to a space (for a UNION INTERSECT) returns an error? Well, yes and no. Yes, you will end up with # NULL! in the cell, which certainly seems a mistake. But like most Excel "errors" no longer a status message – it tells us that when you cross these two ranges, the return value is zero – of course it is: the two strands do not cross!
In order not to have an error, would have to write something like this:
= SUM (A5: E5 C1: C10)
Where are crossed? In cell C5 – and the role of this change that is cell C5. Changing it to
= SUM (A5: E8 C1: D10)
the intersection would be much bigger – the cell range C5: D8 – And since we are using the SUM function, the return value would be the total of all these cells.
But back to our original question – What if the coma, and this operator intersect give you what you need?
I was working with a client recently had a dataset that was not huge, but were encountering difficulties get the answers you need. Had, in column A, a set of unique names – in this case the names of the territories around the world and program providers TV in those territories. In row 1, there were several TV genres – documentary, comedy, reality testing and the like. In the center was the rate of each type of program in each territory.
Here is a sample of the kinds of things:
On another sheet, the client had a space where you select a realm name, select a type of program, and then recover the tax rate applicable.
How to return rate? Like this (I've simplified this slightly to show all relevant parties on the same page above, and the data set considerably smaller:
* = VLOOKUP ($ E $ 11, RateLookupTable, VLOOKUP ($ E10, RateLookupTable, 26, FALSE), 0) $ C $ 9)
Did you get that? Now I consider myself a reasonably user control of Excel, but I still took a while to figure out what was going on here … Look down the left column, with VLOOKUP until we find the territory entered by the user in cell E10. Look across the column 26, which contains only sequential numbers, and that tells us that the row we are in Use that number to feed HLOOKUP, locate the genre written in cell E11, and down the rows, however, many had noted earlier …. Too many formulas as well, and is scientifically proven that the brain begins to melt.
And so I thought it would be better way. There should be a function that read "Look up this value in the top, and this value from the left, and return the value that intersect." The INTERSECT operator we have seen before – space – does not work, since it is based on providing us with the two strings intersect, and of course we know that information. And look although it might, I could not find a function to do the job.
So, I decided to write one.
The basic concept is simple. Specify a range of analysis. Specify the header row to search. Specify the title of the column to search. Returns the value in the row meet and cross the column.
We're here helped by the fact that VBA provides a function INTERSECT, two strings, returns the value at the crossroads of these two. And we can use the Range object method Find to locate the data provided by the user.
Here is the code – commented in his case:
The code also appears at the bottom of this article in plain text, so that you can copy and paste into your own file if desired.
Of course, it is very possible that with a little work function can be improved – to start no error handling. But I think he will agree that compared with our user-defined function to the original and HLOOKUP used, this is quite easy:
= Intersection (RateLookupTable, E11, E12)
This is just the sort of challenge that makes fun working with customers – and it's just the kind of challenge we would be in Excel VBA Of course. If you would like to see expand your skills, why not take a look at our course contents Excel training and see if we can put something together for you or your company.
Here is the code again, the selected text ….
_ Public Function Junction
(OverallRange As Range, _
RowHeading As String, _
ColHeading As String) As Variant
'The required parameters:
'OverallRange – The data range for
'RowHeading – the value of starting to look to the left
'ColHeading – The value of starting to look at the top
'Return value:
'A variant, being what it is at the intersection, or # VALUE! error
'The Find method returns a range, so we need to range variables
RowHeads As Range Dim, ColHeads As Range
'Strength of feature to recalculate if the data changes, as a normal function
Application.Volatile (true)
"Look for the header row in the first column approved range
. Set RowHeads = OverallRange.Columns (1) Find (what: = RowHeading, Lookin: = xlValues, LookAt: = xlWhole)
"Locating cabbage starting in the first row of the range approved
. Set ColHeads = OverallRange.Rows (1) Find (what: = ColHeading, Lookin: = xlValues, LookAt: = xlWhole)
'Intersection Using VBA function to find where the row and column that intersect we have found
Intersection intersection = (RowHeads.EntireRow, ColHeads.EntireColumn). Value
End Function
About the Author
Andrew Richards is the Managing Director of The IT Service – one of South-East England’s leading providers of training and consultancy services in Microsoft Office.
The IT Serice offers training on all Microsoft Office products, specialising in Microsoft Access training courses, Microsoft Excel training courses and Office VBA training courses.
|
|
Microsoft Access 2007 Computer Based Training DVD Rom – Learn MS Access with 6 Hours of Lessons on CD That Are Well Organized From Basic to Advanced Features. Over 225 Access Features Explained By an Experienced MS Office Instructor: Brush up on Your Computer Software Skills with CBT Data Base / dBase / Database Training $39.95 With approximately 6 hours of instruction, this comprehensive End-User Computer-Based Training Module for Microsoft Access 2007 features Step-by-step video instruction led by an experienced Access instructor The CBT includes instruction on the … |
|
|
AutoDesk Inventor Video Tutorial – Training DVD / Download – Inventor 2009-2012 $147.96 VTN is an Authorized AutoDesk Publisher, a presenter at the AutoDesk University, and the leading provider of self-study Inventor video training. Our video tutorials take you step by step through the principal environments of Sketching, Part Design, Drawing, Assembly, Weldments, Inventor Studio, VBA API, Sheet Metal, and Surface Design, building many types of models and projects in Inventor 2009, … |
|
|
VBA FOR MODELERS $76.2 Excellent Condition Used |
|
|
Excel: VBA in Depth (CD) for Mac,Win $36.95 In Excel: VBA in Depth, Excel and VBA expert Curt Frye introduces object oriented programming and shows how to automate routine tasks and provide custom functionality to enhance Excel performance and efficiency. This course introduces the Visual Basic for Applications programming language, covers creating subroutines and functions to hold code, and provides a solid grounding in the Excel 2007 object model. Programming techniques are demonstrated through real world examples. Exercise files accompany the course. ISBN: 1 59671 651 7 UPC: 6 70557 02909 2 SKU: 02909 Duration: 3 Hours On 1 CD Includes Closed Captions |
|
|
COMPUTING WITH EXCEL+VBA $115.35 Brand New |
