Why Loops?

The purpose of a loop is to get Excel to repeat a piece of code a certain number of times. How many times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this for as many times as there are rows of data).Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of different ways to use loops.There is two basic kinds of loops, both of which are demonstrated here:

• Do…Loop and
• For…Next loops.

The code to be repeated is placed between the key words.

Open the workbook Using Loops in VBA in Microsoft Excel.xls and take a look at the four worksheets. Each contains two columns of numbers (columns A and B). The requirement is to calculate an average for the numbers in each row using a VBA macro.

Now open the Visual Basic Editor (Alt+F11) and take a look at the code in Module1.  You will see a number of different macros. In the following exercises, first run the macro then come and read the code and figure out how it did what it did.

You can run the macros either from the Visual Basic Editor by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Visual Basic Editor by using Debug > Step Into (by pressing F8) so you can watch them as they work.

Instruction

If Developer Tab is not in the Ribbon..

• Open Excel.
• Go to VBA Editor (press Alt + F11)
• Go to Immediate Window. ( Ctrl + G)
• Write below Code.
  • Application.ShowDevTools = True

How to Insert VBA code in Excel

• Go to Developer Tab > Code Group > Visual Basic
• Click Insert > Module.
• Will open a Blank Module for you.
• Write / Paste provided code in that Module

How to Run VBA code in Excel

• Select anywhere in between the Code, Sub… End Sub
• Click Run & Run Sub or F5

Exercise 1: Do… Loop Until…

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is Column B is filled or NotEmpty.

Select cell C2 before run the macro as macro is based on ActiveCell.

Here’s the code:

Sub Loop1()
'This loop runs until there is nothing in the Previous column
Do
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

This macro places a formula into the active cell, and moves into the next cell down. It uses LoopUntil to tell Excel to keep repeating the code until the cell in the adjacent column (column B) is empty. In other words, it will keep on repeating as long as there is something in column B.

=Average(RC[-1],RC[-2]) is same as =AVERAGE(B1,A1) with respect to C1,  in R1C1 style, it’s trying to say, that, go for average of One Column left (c-1) and Two Column left (C-2)’ s data.

    Delete the data from Column C and ready for the next exercise

Exercise 2: Do While… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

Select cell C2 before run the macro as macro is based on ActiveCell.

Sub Loop2()
'This loop runs as long as there is something in the Previous column
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

The function IsEmpty = False means “Is Not Empty”

This macro does the same job as the last one using the same parameters but simply expressing them in a different way. In previous code, we are first running the Do… Loop code, then checking if criteria matched or not, where,

In this code, we are first checking the condition, and if matched then we are running the Do… Loop.

It uses Loop Until to tell Excel to if adjacent column (column B) is not empty, then only repeat the code.

Delete the data from Column C and ready for the next exercise

Exercise 3: Do While Not… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

Select cell C2 before run the macro as macro is based on ActiveCell.

Here’s the code:

Sub Loop3()
'This loop runs as long as there is something in the Previous column
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This macro makes exactly the same decision as the last one but just expresses it in a different way. IsEmpty = False means the same as Not IsEmpty. Sometimes you can’t say what you want to say one way, so VBA often offers an alternative syntax.

IsEmpty(ActiveCell.Offset(0, -1)) = False & Not IsEmpty(ActiveCell.Offset(0, -1)), are same way to say, do the repeated task, until adjacent left cell is filled with something, or not Empty.

Delete the data from Column C and ready for the next exercise

Exercise 4: Including an IF statement

The object of this macro is as before, but without replacing any data that may already be there.

Move to Sheet2, select cell C2 and run the macro Loop4.

Sub Loop4()
' This loop runs as long as there is something in the Previous column
' It does not calculate an average if there is already something in the cell
Do
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

The previous macros take no account of any possible contents that might already be in the cells into which it is placing the calculations. This macro uses an IF statement that tells Excel to write the calculation only if the cell is empty. This prevents any existing data from being overwritten.

The line telling Excel to move to the next cell is outside the IF statement because it has to do that anyway.

Exercise 5: Avoiding Errors

This macro takes the IF statement a stage further, and doesn’t try to calculate an average of cells that are empty.

First, look at the problem. Move to Sheet3, select cell C2 and run the macro Loop4.

Note that because some of the pairs of cells in columns A and B are empty, the =AVERAGE function throws up a #DIV/0 error (the Average function adds the numbers in the cells then divides by the number of numbers – if there aren’t any numbers it tries to divide by zero and you can’t do that!).

Sub Loop5()
' This loop runs as long as there is something in the NEXT column
' It does not calculate an average if there is already something in the cell
' nor if there is no data to average (to avoid #DIV/0 errors).
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
ActiveCell.Value = “”
Else
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Note that this time there are no error messages because Excel hasn’t tried to calculate averages of numbers that aren’t there, and criteria checking cell has been changed from adjacent column (B0 to adjacent Column D, as for testing purpose, we have change some of Cell in column B to Empty.

In this macro there is a second IF statement inside the one that tells Excel to do something only if the cell is empty. This second IF statement gives excel a choice. Instead of a simple If there is an If and an Else. Here’s how Excel reads its instructions…

“If the cell has already got something in, go to the next cell. But if the cell is empty, look at the corresponding cells in columns A an B and if they are both empty, write nothing (“”). Otherwise, write the formula in the cell. Then move on to the next cell.”

Exercise 6: For… Next Loop

If you know, or can get VBE to find out, how many times to repeat a block of code you can use aFor… Next loop.

select cell C2 and then run the macro Loop6.

Sub Loop6()
' This loop repeats for a fixed number of times determined by the number of rows
' in the range
Dim i As Long
lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = 0 To lastcell
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Next i
End Sub

This macro doesn’t make use of an adjacent column of cells like the previous ones have done to know when to stop looping. Instead it counts the number of rows in Column A and find the last filled cell by using below method.

Range(“A” & Cells.Rows.Count).End(xlUp).Row

That means, it goes to the last cell in A column (In case of Excel 2007 prior,  cells # A65536 ( 2^16), and for excel 2007 +  Cell Number A10485776 ( 2 ^ 20))  and then in come One Step Up, with pressing Control Key.. End(XlUp) and uses the For… Next method to tell Excel to loop that number of times.

In between any stage, if need to exit the loop, we can use EXIT FOR keyword to exit the FOR LOOP.

Exercise 7: Getting the Reference From Somewhere Else

In the above code we have checked Column A, to set the No Of Repeating Time,

 Range(“A” & Cells.Rows.Count).End(xlUp).Row

Instead of “A” we can use any other cell, to set the lastCell. By doing something like..

Range(“G” & Cells.Rows.Count).End(xlUp).Row

If you wanted to construct a loop that always ran a block of code a fixed number of times, you could simply use an expression like:

Instead of For i = 0 To LastCell , we can expression like of For i = 0 To 23. It will loop through Rows, and increase i form 0 to 23 / lastCell

Exercise 8: About Doing Calculations…

All the previous exercises have placed a calculation into a worksheet cell by actually writing a regular Excel function into the cell (and leaving it there) just as if you had typed it yourself. The syntax for this is:

ActiveCell.FormulaR1C1 = “TYPE YOUR FUNCTION HERE”

These macros have been using:

ActiveCell.FormulaR1C1 = “=Average(RC[-1],RC[-2])”

Because this method actuall change, just like regular functions – because they are regular functions. The calculating gets done in Excel because all that the macro did was to write the function.

If you prefer, you can get the macro to do the calculating and just write the result into the cell. VBA has its own set of functions, but unfortunately AVERAGE isn’t one of them. However, VBA does support many of the commoner Excel functions with its WorksheetFunction method.

On Sheet1 select cell C2 and run the macro Loop1.

Take a look at the cells you just filled in. Each one contains a function, written by the macro.

Now delete the contents from the cells C2:C20, select cell C2 and run the macro Loop8.

Here’s the code:

Sub Loop7()
Do
ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _
ActiveCell.Offset(0, -2).Value)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Take a look at the cells you just filled in. This time there’s no function, just the value. All the calculating was done by the macro which then wrote the value into the cell.

Excel is Fun.

Why Loops?

The purpose of a loop is to get Excel to repeat a piece of code a certain number of times. How many times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this for as many times as there are rows of data).Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of different ways to use loops.There is two basic kinds of loops, both of which are demonstrated here:

• Do…Loop and
• For…Next loops.

The code to be repeated is placed between the key words.

Open the workbook Using Loops in VBA in Microsoft Excel.xls and take a look at the four worksheets. Each contains two columns of numbers (columns A and B). The requirement is to calculate an average for the numbers in each row using a VBA macro.

Now open the Visual Basic Editor (Alt+F11) and take a look at the code in Module1.  You will see a number of different macros. In the following exercises, first run the macro then come and read the code and figure out how it did what it did.

You can run the macros either from the Visual Basic Editor by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Visual Basic Editor by using Debug > Step Into (by pressing F8) so you can watch them as they work.

Instruction

If Developer Tab is not in the Ribbon..

• Open Excel.
• Go to VBA Editor (press Alt + F11)
• Go to Immediate Window. ( Ctrl + G)
• Write below Code.
  • Application.ShowDevTools = True

How to Insert VBA code in Excel

• Go to Developer Tab > Code Group > Visual Basic
• Click Insert > Module.
• Will open a Blank Module for you.
• Write / Paste provided code in that Module

How to Run VBA code in Excel

• Select anywhere in between the Code, Sub… End Sub
• Click Run & Run Sub or F5

Exercise 1: Do… Loop Until…

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is Column B is filled or NotEmpty.

Select cell C2 before run the macro as macro is based on ActiveCell.

Here’s the code:

Sub Loop1()
'This loop runs until there is nothing in the Previous column
Do
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

This macro places a formula into the active cell, and moves into the next cell down. It uses LoopUntil to tell Excel to keep repeating the code until the cell in the adjacent column (column B) is empty. In other words, it will keep on repeating as long as there is something in column B.

=Average(RC[-1],RC[-2]) is same as =AVERAGE(B1,A1) with respect to C1,  in R1C1 style, it’s trying to say, that, go for average of One Column left (c-1) and Two Column left (C-2)’ s data.

    Delete the data from Column C and ready for the next exercise

Exercise 2: Do While… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

Select cell C2 before run the macro as macro is based on ActiveCell.

Sub Loop2()
'This loop runs as long as there is something in the Previous column
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

The function IsEmpty = False means “Is Not Empty”

This macro does the same job as the last one using the same parameters but simply expressing them in a different way. In previous code, we are first running the Do… Loop code, then checking if criteria matched or not, where,

In this code, we are first checking the condition, and if matched then we are running the Do… Loop.

It uses Loop Until to tell Excel to if adjacent column (column B) is not empty, then only repeat the code.

Delete the data from Column C and ready for the next exercise

Exercise 3: Do While Not… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

Select cell C2 before run the macro as macro is based on ActiveCell.

Here’s the code:

Sub Loop3()
'This loop runs as long as there is something in the Previous column
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

This macro makes exactly the same decision as the last one but just expresses it in a different way. IsEmpty = False means the same as Not IsEmpty. Sometimes you can’t say what you want to say one way, so VBA often offers an alternative syntax.

IsEmpty(ActiveCell.Offset(0, -1)) = False & Not IsEmpty(ActiveCell.Offset(0, -1)), are same way to say, do the repeated task, until adjacent left cell is filled with something, or not Empty.

Delete the data from Column C and ready for the next exercise

Exercise 4: Including an IF statement

The object of this macro is as before, but without replacing any data that may already be there.

Move to Sheet2, select cell C2 and run the macro Loop4.

Sub Loop4()
' This loop runs as long as there is something in the Previous column
' It does not calculate an average if there is already something in the cell
Do
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

The previous macros take no account of any possible contents that might already be in the cells into which it is placing the calculations. This macro uses an IF statement that tells Excel to write the calculation only if the cell is empty. This prevents any existing data from being overwritten.

The line telling Excel to move to the next cell is outside the IF statement because it has to do that anyway.

Exercise 5: Avoiding Errors

This macro takes the IF statement a stage further, and doesn’t try to calculate an average of cells that are empty.

First, look at the problem. Move to Sheet3, select cell C2 and run the macro Loop4.

Note that because some of the pairs of cells in columns A and B are empty, the =AVERAGE function throws up a #DIV/0 error (the Average function adds the numbers in the cells then divides by the number of numbers – if there aren’t any numbers it tries to divide by zero and you can’t do that!).

Sub Loop5()
' This loop runs as long as there is something in the NEXT column
' It does not calculate an average if there is already something in the cell
' nor if there is no data to average (to avoid #DIV/0 errors).
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
ActiveCell.Value = “”
Else
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

Note that this time there are no error messages because Excel hasn’t tried to calculate averages of numbers that aren’t there, and criteria checking cell has been changed from adjacent column (B0 to adjacent Column D, as for testing purpose, we have change some of Cell in column B to Empty.

In this macro there is a second IF statement inside the one that tells Excel to do something only if the cell is empty. This second IF statement gives excel a choice. Instead of a simple If there is an If and an Else. Here’s how Excel reads its instructions…

“If the cell has already got something in, go to the next cell. But if the cell is empty, look at the corresponding cells in columns A an B and if they are both empty, write nothing (“”). Otherwise, write the formula in the cell. Then move on to the next cell.”

Exercise 6: For… Next Loop

If you know, or can get VBE to find out, how many times to repeat a block of code you can use aFor… Next loop.

select cell C2 and then run the macro Loop6.

Sub Loop6()
' This loop repeats for a fixed number of times determined by the number of rows
' in the range
Dim i As Long
lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = 0 To lastcell
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Next i
End Sub

This macro doesn’t make use of an adjacent column of cells like the previous ones have done to know when to stop looping. Instead it counts the number of rows in Column A and find the last filled cell by using below method.

Range(“A” & Cells.Rows.Count).End(xlUp).Row

That means, it goes to the last cell in A column (In case of Excel 2007 prior,  cells # A65536 ( 2^16), and for excel 2007 +  Cell Number A10485776 ( 2 ^ 20))  and then in come One Step Up, with pressing Control Key.. End(XlUp) and uses the For… Next method to tell Excel to loop that number of times.

In between any stage, if need to exit the loop, we can use EXIT FOR keyword to exit the FOR LOOP.

Exercise 7: Getting the Reference From Somewhere Else

In the above code we have checked Column A, to set the No Of Repeating Time,

 Range(“A” & Cells.Rows.Count).End(xlUp).Row

Instead of “A” we can use any other cell, to set the lastCell. By doing something like..

Range(“G” & Cells.Rows.Count).End(xlUp).Row

If you wanted to construct a loop that always ran a block of code a fixed number of times, you could simply use an expression like:

Instead of For i = 0 To LastCell , we can expression like of For i = 0 To 23. It will loop through Rows, and increase i form 0 to 23 / lastCell

Exercise 8: About Doing Calculations…

All the previous exercises have placed a calculation into a worksheet cell by actually writing a regular Excel function into the cell (and leaving it there) just as if you had typed it yourself. The syntax for this is:

ActiveCell.FormulaR1C1 = “TYPE YOUR FUNCTION HERE”

These macros have been using:

ActiveCell.FormulaR1C1 = “=Average(RC[-1],RC[-2])”

Because this method actuall change, just like regular functions – because they are regular functions. The calculating gets done in Excel because all that the macro did was to write the function.

If you prefer, you can get the macro to do the calculating and just write the result into the cell. VBA has its own set of functions, but unfortunately AVERAGE isn’t one of them. However, VBA does support many of the commoner Excel functions with its WorksheetFunction method.

On Sheet1 select cell C2 and run the macro Loop1.

Take a look at the cells you just filled in. Each one contains a function, written by the macro.

Now delete the contents from the cells C2:C20, select cell C2 and run the macro Loop8.

Here’s the code:

Sub Loop7()
Do
ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _
ActiveCell.Offset(0, -2).Value)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Take a look at the cells you just filled in. This time there’s no function, just the value. All the calculating was done by the macro which then wrote the value into the cell.

Excel is Fun.

Add a Row to an AutoFilter with VBA

Adding a row to the bottom of an AutoFilter is something we do often enough but doing it without code requires more clicks than I would like - copy an existing row, select the row below where the existing bottom row is, then opening up Paste Special. We probably want to paste both Formats and Formulas, so we have do one, reopen Paste Special and do the it again. Okay, things have improved slightly with Excel 2010, but even so...

So, let's use some VBA to help reduce some RSI.

Sub AddRowToFilter()

    On Error Resume Next

    With ActiveSheet.AutoFilter.Range

        .Rows(2).Copy

        With .Offset(.Rows.Count).Rows(1)
            .PasteSpecial xlPasteFormats
            .PasteSpecial xlFormulas
        End With

    End With

    Application.CutCopyMode = False

    If Err <> 0 Then MsgBox "This sheet has no filter"

    On Error GoTo 0

Msgbox "Excel is really fun ;)"


End Sub

Note the message box just in case there is no filter.

Excel is fun :)

How to Lookup Values to Left?

Situation

There is no argument that VLOOKUP is a beautiful & useful formula. But it suffers from one nagging limitation. It cannot go left.

Let me explain, Imagine you have data like below. Now, if you want to find-out who made $2,133 in sales, there is no way VLOOKUP can come to rescue. This is because, once you search a list using VLOOKUP, you can only return corresponding items from the column at right, not at left.

Data:

One easy fix would be move the sales data to the left of person name. But this is an annoying fix, because, god knows you may want to lookup based on profit values or something else in future. A better alternative is,…

Solution

.., to use a formula combination called INDEX + MATCH (or OFFSET + MATCH would work too).

The basic syntax of this combination is like this: =INDEX(column with data you want,MATCH(value you are looking for, column which contains this data,0)). So, for eg: =INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0))would find the position of 1088 in list D5:D17 and return corresponding element from B5:B17 (ie the value from left). See more examples below.

Examples:

Try this Excel is Fun.

How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel

The following examples use the INDEX and MATCH worksheet functions to find a value based on multiple criteria.

Example 1: Data in Columns

Method 1

1.       Start Excel.

2.       Type the following data into a new worksheet:

3.     A1: Part   B1:  Code   C1:  Price   D1:  Find Part  E1:  Find Code

4.     A2: x      B2:  11     C2:  5.00    D2:  y          E2:  12

5.     A3: x      B3:  12     C3:  6.00    D3:  y          E3:  11

6.     A4: y      B4:  11     C4:  7.00    D4:  x          E4:  12

7.     A5: y      B5:  12     C5:  8.00    D5:  x          E5:  11

                                     

8.       To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

9.       Press CTRL+SHIFT+ENTER to enter the formula as an array formula.

The formula returns the value 8.00.

10.    Select cell F2, grab the fill handle, and then fill down to cell F5 to retrieve the price for each part and code combination.

Method 2

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against more than two criteria because it does not require nested IF statements. This method is identical to Method 1 except that you replace the formula in step 3 with the following formula:

=INDEX($C$2:$C$5,MATCH(D2&E2,$A$2:$A$5&$B$2:$B$5,0))

Example 2: Data Arranged in Rows

Method 1

1.       Start Excel.

2.       Type the following data into a new worksheet:

3.     A1: Part        B1: x      C1: x     D1: y       E1: y

4.     A2: Code        B2: 11     C2: 12    D2: 11      E2: 12

5.     A3: Price       B3: 5.00   C3: 6.00  D3: 7.00    E3: 8.00

6.     A4: Find Part   B4: y      C4: y     D4: x       E4: x

7.     A5: Find Code   B5: 12     C5: 11    D5: 12      E5: 11

                                     

8.       To retrieve the price for part y with code 12 and return the value to cell B6, type the following formula in cell B6:

=INDEX($B$3:$E$3,MATCH(B4,IF($B$2:$E$2=B5,$B$1:$E$1),0))

9.       Press CTRL+SHIFT+ENTER to enter the formula as an array formula.

The formula returns the value 8.00.

10.    Select cell B6, grab the fill handle, and then fill right to cell E6 to retrieve the price for each part and code combination.

Method 2

A second method yields the same results but uses concatenation instead. The following sample formula may be better for matching data against more than two criteria because it does not require nested IF statements. This method is identical to Method 1 (under Example 2) except that you replace the formula in step 3 with the following formula:

=INDEX($B$3:$E$3,MATCH(B4&B5,$B$1:$E$1&$B$2:$E$2,0))

EXCEL is Fun..!!!

WorkBook Events – Don’t save with Freeze Panes

Excel lets us control things through events. This article isn’t a detail discussion on what events are or their features. Instead it gives an example of a WorkBook Event. These are events that are held at the workbook level rather than a particular worksheet.

Through VBA we can control what happens at certain events such as before printing or before saving. One common problem that I face is that a number of people I work with don’t like files with Freeze Panes on them.

So in this article, we will put together some code that will check if Freeze Panes is on and if so, it won’t save the file. This means that I have to save it without freeze panes – keeping my colleagues happy !!

The most important thing about workbook events is that they should be saved in the correct place – at the workbook level.

To access the workbook level, follow the steps below:

1. Right click on an Excel workbook – view code:

2. This will bring up:

3. Double click on “This Workbook” and then select “WorkBook” from the first drop down on the left hand side:

We see that the value on the left hand side has now changed to “Open” – with some code for the Workbook Open Event. This code will let us determine what happens when the workbook opens for the first time.

However we want to control what happens when we save the workbook. So change the right hand drop down to “Before Save” . The screen will now look like:

We now insert the following code after the declaration:

If ActiveWindow.FreezePanes = True Then

    MsgBox "Freeze Panes is on - File is NOT SAVED"

    Cancel = True

End If

So that the complete code now looks like:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ActiveWindow.FreezePanes = True Then

    MsgBox "Freeze Panes is on - File is NOT SAVED"

    Cancel = True

End If

End Sub

Now save the file and THEN activate Freeze Panes in any window. Then – RESAVE the file. A msgbox will appear stating that “Freeze Panes” is on – and the file is not saved.

Indeed the file will not save until Freeze Panes is removed.

Please feel free to ask if you need any answer.

EXCEL is Fun..!!!