PDA

View Full Version : Solved: Inserting columns late - hours of regression testing



Pool Master
04-08-2008, 09:26 AM
Hi All,

I'm running into an issue with my macros and was wondering if anyone has a "best practice" that they follow to prevent this from occuring. After I finalize a macro driven report for my company they request column additions periodically. This causes me to need to revise all of my formulas and range references since everything to the right of the inserted column shifts. I know that I could cut the added columns and move them out of the table prior to running the finalized macro and insert them back in after, but this can get ugly especially when debugging the program. Does anyone have a suggestions for makes these ranges/formulas more dynamic in the macro code, so they can handle late inserted columns?

Thank you!
Adam

mdmackillop
04-08-2008, 11:45 AM
You could search a header row to get column numbers and use that instead of hard coded values
eg

Sub test()
qty = Rows(1).Find("Quantity").Column
Cells(2, qty) = "Test"
End Sub

rbrhodes
04-09-2008, 07:49 PM
Hi Adam,

I use a variable for rows and columns. Set it once at the beginning of the code and use the variable all the way thru. Then if you need to adjust a reference you change it once.

ie:



Dim Cols as long
Dim Rowz as long

'Set and /or change here
Rowz = 4
Cols = 8

'then use Cells(Row variable, Column variable) instead of Range("X#") in the code

Cells(Rowz,Cols)= something
Cells(Rowz +2,Cols - 3) = something




You could also write a worksheet change macro to update your code variables if a column is inserted, but thats a little more advanced!

Pool Master
04-11-2008, 04:49 PM
Mdmackillop thank you for your recommendation. I've implemented this change successfully and not only are the columns now variable, but the calculations and range references are now easier to comprehend.

Thank you for your assistance!
Adam