Solved: Inserting columns late - hours of regression testing

[Pool Master]

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]

You could search a header row to get column numbers and use that instead of hard coded values
eg
[VBA]
Sub test()
qty = Rows(1).Find("Quantity").Column
Cells(2, qty) = "Test"
End Sub

[/VBA]

[rbrhodes]

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:

[vba]

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

[/vba]


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]

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