Auto Adjust VBA Code

[skankingpigeon]

Hey,

Probably a really stupid question, but is there a way to make VBA code auto adjust when you change the worksheet?

For example, my VBA code is looking into A40, but if I want to modify my sheet and add a row at A10, the VBA code will still be looking at A40, when it should be looking at A41. Its a massive pain as i've made a huge amount of coding in a workbook and need to modify it now.

Thanks!

Joe

[Leith Ross]

Hello Joe,

To provide you a solution, you should post your workbook with before and after examples.

[Miraun]

Hey Skank... (one of the better truncated names I've written to)

Short answer: Not that I know of

Long answer: You have two options available to you.

1) If you have a header row/column, you can use a search function to find the old A40 record, and work off of offsets from there rather than hard coding the row position.

2) You can set up a separate worksheet with cell contents that contain cell references that you can manually update, and use these as variables in your VBA. Like in A1, put in A40, and then in the VBA, just declare the range variable equal to the text in A1. (It makes sense to me... I hope you can follow what I'm saying)

[skankingpigeon]

Thanks Miraun. I just wanted to know if there was a way to switch the auto adjust on in VBA.

What i'll probably do is just name all my ranges. I'll have to make adjustments this time but won't have to anytime afterwards.

Thanks Again!!

[Mordred]

I don't see how naming your ranges will help you if rows are not inserted, your named ranges will not grow. Is A40 or A41 your last row in your data set?