2012-04-29, 04:28 #1
- Join Date
- Apr 2012
- Thanked 0 Times in 0 Posts
Need Excel Macro to add diff columns
I have a multi-worksheet Excel file that I need to add difference columns to. I also need to be able to specify which worksheets this should apply to, as well as what the first column needs to be. My issue is as follows...
I have a variety of columns, let's say A.B.C.D.E.F... I need to be able to say 'insert a diff column for C/D, E/F that subtracts the percentages shown in column D from C, and F from E. That said, I want to be able to specify which columns to create the diff columns for, and which worksheets I want to create diff columns for. Note that on some worksheets, I only want B vs. C, and others I want to say B vs. C, D vs. E, etc.
Finally, I want to be able to have control over whether the macro is creating a diff column (subtraction), or a division column (e.g. 7%/5% in columns B vs. C).
I understand this can take multiple Macros, but thought I'd throw it out there for others to weigh in on.
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2012-05-01, 11:30 #2
- Join Date
- Mar 2002
- Newcastle, UK
- Thanked 171 Times in 167 Posts
In the sample file attached I have two macros that insert either a new column for Difference or a new column for Ratio.
I have assigned short-cut keys for these macros:
Ctrl-Shift-D (to insert a new Difference column)
Ctrl-Shift-R (to insert a new Ratio column)
There are a couple of data sheets for you to test these macros.
The macros assume your headings are in row 1.
To use the macros, place your cellpointer where you want a new column inserted.
For example, if you wanted the difference between columns [C] and [D]:
Place your cellpointer (anywhere) in the adjacent column [E].
..Press Ctrl-Shift-D (to insert a new Difference column), or..
..Press Ctrl-Shift-R (to insert a new Ratio column)
To make the inserted new columns stand out, I have included code to colour the new columns.
You can comment these out in the VBA code or adjust to suit.
To use these macros in your existing workbooks, simply copy the code into your workbooks.
You could assign the macros to yourown shortcut keys or to a custom button in your top-panel toolbar.