Macros finished now I need to add columns

[kdunleav]

I have completed worksheet that is all macro driven. I now need to add a couple of columns. The problem is when I add columns none of my macros adjust to the columns moving over. For example AA originally had a formula in it, now I added a column so AA is now AB. How can I have the Macros follow the column they were originally in? Any help would be great thank you.

 

[shabbyporpoise]

were you using macro recorder? maybe this is part of the issue?

 

[kdunleav]

Yes, i was using macro recorder

 

[shabbyporpoise]

I am new too excel VBA, so my advice probably won't be too helpful. I was hoping my question would spark the help of another more experienced user. From what I know about macro recorder when you look at the code using alt-F11 there will be a lot of ".selection" stuff in there. I'm not confident that this will have an affect on your code when items change in the sheet. you may want to go into the code and change some cell ranges so that they are hard-coded.

All the code I have programmed, the specific cells are hard-coded, and I can call those cells as if they were objects.

I may have time later to send a copy of a sample code of mine involving adding columns, currently I have to a meet a work deadline.

Good luck

 

[shabbyporpoise]

hi kdunleav
here is one option. There are other ways to add columns as well. I grabbed this one from a program I wrote a few days ago. You will notice that I am using array to achieve this. hope it helps

sub twoDimensionalArray()
dim classics (1 to 100, 1 to 100) as string
dim intRow as integer, intColumn as integer
dim numRow as integer, numColumn as integer

numRow=inputbox"input number of rows"
numColumn=inputbox"input number of columns"'these are so that the user 'can specify how many rows and columns they want. then the for...next 'loop will iterate the amount of times they desired
    for intRow=1 to numRow
    for intColumn=1 to numColumn
        classics(intRow,intColumn)=cells(intRow,intColumn).value
    next intColumn
    next intRow
end sub