VBA Express : Excel - Automatically Adjust Value by a Given Amount

Excel

Automatically Adjust Value by a Given Amount

Ease of Use

Easy

Version tested with

2000

Submitted by:

Brandtrock

Description:

Values entered into cell F6 in the spreadsheet will be adjusted by the specified operation and value. In this example, divided by 1000. This can be applied to the entire sheet if desired.

Discussion:

In preparing financial statements expressing dollar figures in thousands, I had previously used the fixed decimal option on the Edit tab of the Tools>Options menu selection. Multiplying a cell by a fixed amount (such as 1.0785 to reflect the total price of an item including the sales tax) was not achievable in this fashion, however. This code can be modified to add, subtract, or multiply by a standard amount by replacing the "/ 1000" with the correct operator and the desired standard amount. This method can be employed on all cells of the active sheet by eliminating the following line of code: If Target.Address <> "$F$6" Then Exit Sub

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Address <> "$F$6" Then Exit Sub 
    If Target.HasFormula Or IsEmpty(Target) Then Exit Sub 
    If IsNumeric(Target) Then 
        Application.EnableEvents = False 
        Target.Value = Target.Value / 1000 
        Application.EnableEvents = True 
    End If 
End Sub

How to use:

Copy the code above.
From the workbook you want to use the code in, hit ALT-F11 to get the Visual Basic Editor (VBE) opened.
Select your workbook as the VBA Project from the window on the left.
Choose worksheet in the left drop down box near the top of the window.
Choose change in the right drop down box near the top of the window.
Paste the code (or type it manually) in the window of the Visual Basic Editor.
Close the VBE and return to your spreadsheet.

Test the code:

Enter a value in a cell, the number should display normally.
Enter a value in cell F6.
The number that displays should be the number you entered adjusted by the value in your code (divided by 1000 in this case).

Sample File:

Divided_By_1000.zip 5.98KB

Approved by mdmackillop

This entry has been viewed 139 times.