Ask Your Question
0

Need to add a column to a sheet without messing up all the coding

asked 2020-06-28 01:22:44 +0200

Isoka gravatar image

Hi there,

I am not a professional programmer but every now and then I am required to create macros to perform complex actions in worksheets. For that I go online to study specifically what I need and frequently adapt codes I find. Now I was asked to add a column to a sheet that has a very long code (made by myself with the above mentioned technique ;) which relies on the existing column numbers to work. I imagine programmers face this situation on a daily basis so I am hopeful I will get help here :) Is there a way to automatically reorganize a macro code so that when new columns are added to the sheet it will add the equivalent number to the positions specified previously? Thanks in advance. Cheers!

Isoka

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-06-28 08:34:46 +0200

keme gravatar image

updated 2020-06-28 08:44:27 +0200

Is there a way to automatically reorganize a macro code so that when new columns are added to the sheet it will add the equivalent number to the positions specified previously?

No.

There are several ways to go about referencing specific sections of a sheet. When you "hardcode" by column number, a find/replace might work. I would never use that in macro code. Too many ways for it to go terribly wrong.

The best general advice I can give you is to separate processing from presentation. If you need to add a column to the processing part, append it to your table (use the first available column outside your current dataset). Presentation can be made through direct references or some extraction technique (filters, pivot table).

If you can share the macro(s) with us and explain what it does (desired result, not inner workings), we may be able to advise on adjusting your code. From what you have given, it is impossible to provide any specific advice.

For the actual question you posed:
Automated code generators exist. They will adjust the generated code according to set parameters. To my knowledge, generalized "code fixing" automata to adjust variable references do not exist. You have to do it yourself.

So the simple answer to your simple question is: "No"

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-06-28 01:22:44 +0200

Seen: 30 times

Last updated: Jun 28