Unfortunately Excel's goal seeking feature forces you to hand-enter a value into the "To value" field of the input box. You can't directly tie this input to a cell like you can with the "Set cell" and "By changing cell" inputs (see the screenshot below).

This can make things difficult especially if you're trying to seek very large values or values that that are carried out to many decimal points.

The following procedure allows you to get around this limitation. First, enter the cell addresses for the variables called Rng1, Rng2, and Rng3. Note that a formula must appear in Rng1 and a value must be entered into Rng3. Next, run the procedure.

To add images of embedded charts to this weblog, I use the VBA procedure below. To run the procedure, first make sure the specifications are correct. You can make changes to the chart height, width, file name, and file path. When all of the inputs are correct, click on (activate) the embedded chart and run the macro.

Please note that the height and width are not really needed. I've added this code only because I like to make sure all of the charts are exactly the same size.

Many companies produce reports at a top-level, department, division, and/or cost center level. When Excel is used to build these reports, the normal practice seems to be to create a new sheet for each department, division, and/or cost center. Each sheet contains the data and calculations for that area. A single top-level reporting sheet is then created to which each department, division, and/or cost center sheet links to. When it's all over and done the workbook can become very large, prone to error, and difficult to audit.

An easier and more accurate approach is to use the combination of lookup formulas and a VBA loop to build the report. In this example, only four sheets in the Excel file are illustrated (it could be only one if designed that way). The first sheet, called "dBase", contains the raw data for each department, division, and/or cost center. The second sheet, called "Calcs", contains a single set of calculations needed to build the final report. The third sheet, called "Load", contains a summary of the data from the Calcs sheet needed for the final report. The final sheet, called "Report", is the final report.

The example begins with a screenshot of the sheet called "dBase" (for database). The "dBase" sheet contains ten departments. The Commerical / Contract and Government volumes for each department is provided for the years 2007 and 2008. The final report requires that you show the components of payer mix variance for each department.

The next screenshot shows the sheet called "Calcs". The blue cell (C2) contains the name of the department. The yellow cells contain lookup formulas that reference the "dbase" sheet.

For example, the lookup formula in cell E8 is:

The lookup formula in cell F8 is:

The next screenshot shows the sheet called "Load". The purpose of this sheet is to reference all of the values that need to appear in the final report into one row of data.

The data that appears in the "Load" sheet is referenced to the green cells in the "Calcs" sheet.

Finally, a VBA procedure is run to create the report. A loop is created that, when combined with the lookup formulas, does the following:

• The VBA procedure loads the name of the department into cell C2 of the "calcs" sheet.
• The lookup formulas in the calcs sheet reference the data from the "dBase" sheet for that particular department.
• The payer mix calculations are performed.
• The payer mix data is referenced from the "calcs" sheet into row of data in the "Load" sheet.
• The data from the "Load" sheet is copied as values into the final report.

In this example, the procedure described above is performed ten times - once for each department as referenced in the range B8:B17 of the "Report" sheet. The VBA code that performs the work is below:

After the macro has been run, the final report looks like this:

The advantages of using lookup formulas and VBA loops to build the report are these:

• Only one set of calculations (the "Calcs" sheet) needs to be audited for accuracy. This is especially advantageous as the number of departments, division, etc. needed on the final report grows.
• The audit risk concerning the workbook is reduced due to less sheets and data ranges needing to be proofed.
• The physical size of the workbook is reduced which make it easier to maintain and transfer to others via email, web, etc.
• The VBA procedure runs fast and the report is produced almost instantaneously.

Potential disadvantages of using lookup formulas and VBA loops to build the report are these:

• If you need to pass the workbook on to someone that has no experience with VBA, the issue of who can maintain the workbook may come up.
• If your manager is clueless when it comes to lookups or VBA, he or she may prefer the "many sheets and calculations" approach so they can understand it (runs hand-in-hand with the first bullet).

The need to fill a blank range with zeros often occurs when dealing with data that is downloaded from a mainframe. Recently I've seen several examples that utilize a loop structure to fill a blank range with zeros. Rather than using loops, below is an example that utilizes the SpecialCells method to fill a range.

The SpecialCells method is much faster than using a loop structure.

A recent post to the Microsoft Excel Charting Newsgroup asked "how can I return a non-integer value from a scrollbar? A VBA approach would be to divide the scrollbar output by 10, 100, 1000, etc. Assuming the scrollbar is named "SB1", the example below returns values between 0.1 and -0.1:

This simple procedure loads the values in the range A1:A5 into an array. The Debug.Print statement prints the contents of the array in the Immedicate Window of Visual Basic Editor.

I'm not sure where I found this so unfortunately I can't credit the original author.

Last weekend I spent several hours searching Excel's Help and the Internet trying to come up with a procedure to reverse the items in a one-dimensional array. I was using an array as the source for a listbox load and the loop below to load the items into the listbox:

Unfortunately this routine loads the data from low to high instead of high to low. After spinning my wheels, I posted my problem to the Microsoft Programming Newsgroup and it was solved within hours. I received three responses and all three worked. Below is the simplest correction:

Thanks to those in the Programming Newsgroup who helped me with this.

I always seem to spend ten minutes trying to find these snippets of code on the Internet when I need them. For reference . . .

To trigger a macro on a change in range, load the procedure below into a sheet module. It will fire when a change is made to the range A1:A5.

To trigger a macro on a change in the sheet, load the procedure below into a sheet module. It will fire when a change is made to any cell on the sheet: