Chapter 35: Globally Changing a Range of Values

Overview

When you have a large amount of data on a spreadsheet, you may wish to change a range of numbers by a set value or a percentage. For example, you may want every value in a range to increase by two, or, if the spreadsheet shows sales figure projections for the coming year, you may want to see the impact of a ten percent change on all numbers across a range. You can use scenarios in Excel to place individual values into cells, but you cannot apply a global factor across a range of cells .

The code in this chapter gives the user the ability to apply a global factor such as adding ten to every cell or decreasing all numbers by five percent. To make it work, a UserForm must be built to allow the user to input a factor. Insert a new UserForm by selecting Insert UserForm from the code menu.

The dialog needs a label control to hold the description of the input, a text box to capture the user input, and two command buttons for OK and Cancel. The title of the form can be changed by clicking the form itself and then changing the caption property in the properties window. Drag the controls from the toolbox onto the form. The UserForm shown in Figure 35-1 is intended as a guide. So long as you have a text box for input, other controls are flexible.

Figure 35-1: Defining a UserForm for setting global values

The OK and Cancel buttons need code attached to them or they will do nothing. If you have not already done so in a previous example, you also need to set up a global variable called canc in the declarations section of your module to hold a value that says whether the user clicks OK or Cancel.

The whole idea of the UserForm is to capture a parameter string that can be applied to a range of cells. If you want to increase all the numbers by three, enter +3 .

 Global canc As Integer 

Add the following code in the Click events for the OK and Cancel buttons. Do not put this in your module. Instead, double-click the OK button and you will go into the code for the Click event.

 Private Sub CommandButton1_Click() 
 UserForm2.Hide 
 canc = 0 
 End Sub 
 
 Private Sub CommandButton2_Click() 
 UserForm2.Hide 
 canc = 1 
 End Sub 

Each of these routines hides the UserForm with the Hide method. This means that execution is transferred back to the original code that displayed the form. However, if the user clicks OK, canc is set to 0 (false); otherwise , it is set to 1 (True). Being that canc is a global variable, you can access it from anywhere . You then insert the following code into a module:

 Sub change_val() 
 UserForm2.Show 
 If canc = 1 Then Exit Sub 
 op = UserForm2.TextBox1.Text 
 Dim addr As String 
 For Each window In Windows 
 For Each Worksheet In window.SelectedSheets 
 
 For Each cell In Application.Selection 
 
 addr = Worksheet.Name & "!" & cell.Address 
 
 On Error Resume Next 
 
 If Range(addr).Value <> "" And IsNumeric(Range(addr).Value) _ 
 And Range(addr).HasFormula = False Then 
 
 Range(addr).Value = "=" & Val(Range(addr).Value) & op 
 
 Range(addr).Copy 
 
 Range(addr).PasteSpecial xlPasteValues 
 
 End If 
 
 Next cell 
 Next worksheet 
 
 Next window 
 End Sub 

The first thing that this code does is to display the UserForm that you inserted, which allows the user to input information into the text box and click OK or Cancel depending on their action.

After the form is hidden in one of the click events, the code tests the variable canc to see if the Cancel button was clicked. If the value of canc is 1 (meaning the Cancel button was pressed), then a simple Exit Sub is all that is required.

If OK was clicked, the input entered into the text box is captured in the variable op . Validation code could be inserted at this point if the user did not input anything, but it makes no difference to subsequent code because all values will remain the same.

A variable called addr is set up as a string. The code then loops through each window in the Windows collection and goes through each worksheet in the selected worksheets collection. The variable addr is loaded with the current worksheet, and the current cell address concatenated with the ! character. The value of the cell defined by addr is then tested to check that it is not empty, that it has a numeric value, and that it is not a formula. If you apply an operand and a value to the cell, there must already be a value that is not a formula for it to work on since the formula value will be automatically updated because of the change to the numeric cells.

If all conditions are met, the cell value is filled with a string containing the = sign concatenated with the existing cell value concatenated with the variable op , which holds the details of how the cell has to change ‚ for example, "*90%".

This puts the whole thing in as a formula. To tidy it up, a copy and paste operation is then performed on that cell, copying the cell to the clipboard using the Copy method. The PasteSpecial method then pastes only the value back into the cell so that only the new number appears.

Place a range of data into a spreadsheet (include some formulas inside it as well) and select it by dragging the cursor across it. Run the code and enter *90% in the text box on the UserForm. Click OK, and all the cells will hold 90 percent of their previous value. Formula cells will be unchanged, but they will show an actual value of 90 percent based on the other cells.

To put the numbers back to their previous values, simply do the reverse. Enter /90% , and they will be back to their starting position.