Posts Tagged ‘Intersect’

Lesson 4 – More on Ranges

September 6, 2012 Leave a comment

After the brief introduction on lesson 3 regarding ranges, this lesson is an attempt to take it a bit further and discuss a bit more details on ranges with a hands on example. The example of this workbook is a table where each column corresponds to a specific month and each row corresponds to a client. What we want to do in this worksheet is to select a client from the drop down list (green cell) and find the first month that he made a transaction and the value of it. For example for Client2 the red cells should display (February, £8,508.24), for Client8 should display (December, £6,362.22) etc.

In this table I have defined two named ranges, the one is the client list (“ClientList”) which corresponds to the yellow cells and the date list (“DateList”) which are the blue cells.

In order to calculate the First Deposit Date and Value (red cells) I have used events in vba code which basically identify if a change has been done in the green coloured client cell. Events however are not the purpose of this chapter so they will be discussed in a following lesson.

So let’s see how this task is achieved. To be consistent with practises in previous lessons, I have added a module and gave a meaningful name to the worksheet object (shtTransactions) as per the screenshot below:

To briefly explain how I calculate the result every time the selected client is changing, excel has ‘Events’. An event can be a workbook open or close, a selection change, a value change etc. In my particular case I am interested in the Worksheet_Change event. The idea is that when this event is fired a piece of code is executed. To add code in this event, I doubled click in VBE the ‘shtTransactions’ and selected worksheet from the first dropdown menu and change for the event I am interested

The parameter (‘Target’) in this sub procedure is a range variable which corresponds to the range in the workbook where the change has occurred. i.e. if I change the value of the client drop down menu (green cell) then my ‘Target’ is the selected client (green cell). I then use an if statement and the Intersect(explained further below) method to check if the change has occurred in the range that I am interested. If this is true, then I call the sFindFirstTransactionData sub proc to calculate the first transaction data for the selected client.

In the main sub proc now, what we do is that we firstly parse the client name that we want to find data from. Then we define two ranges, one for the client list and one for the date (yellow and blue cells in screenshot above). In the outer For Each I loop through the clients trying to find the selected one. Once the selected client is found I then loop through the date list. Then I make use of the Intersect() method (one of my favourites in vba – simple and powerful as it can fit many purposes.) which basically give you the rectangular common part of two ranges. In my case is going to provide me with the transaction value for a particular client for a particular month. So in the code for each intersection I am checking if the cells is blank or not. If it is not blank for the date I am checking it means that the month I am looking for is found and the intersection will provide me with the value. I then put these two values in the red cells.

The Intersection method can be visually presented as below with the purple cell being the intersection of the two bright green ranges, the horizontal and vertical lines.

Thanks for reading!

Excel 2010 has been used for this Lesson. The excel file of the lesson can be sent to you on request(contact page for details).