Hi, I have a worksheet with a macro that I manually run customer data through every week. I have created an additional sheet in the same workbook which uses a series of "if" formulas to populate client name and dates of contact from the macro sheet into a separate sheet. It works fine...until I update the macro....When I run new data, the formulas all change to return data in a different column. So, say that customer name is in column b and last time I spoke to them in column f, when i run the macro, the columns change so now it returns c and g and it's no longer pulling the data I want. How can I prevent this from happening?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Prevent formulas from changing when macro is run
0
Answers
0
In your formula cell references, use absolute references. Put dollar signs in front of the rows and columns in the formula.
This:
=A1would be come this:
=$A$1Do this for your IF statements and none of the references will change no matter where the formulas are moved.
Discussion
Hi Don, Thanks for the idea but my formula already uses absolutes. The problem is that when I run a macro, I think it rewrites data and forcibly changes my formulas. So, as an example before running the macro the formula is :
=IF(AL!$BW2>$A$1,ROW(),"")
when it is run, it changes to:
=IF(AL!$CF2>$A$1,ROW(),"")
=IF(AL!$BW2>$A$1,ROW(),"")
when it is run, it changes to:
=IF(AL!$CF2>$A$1,ROW(),"")
seseley Oct 2, '16 at 3:46 pm
I'd need to see your macro to be able to troubleshoot it. If you edit your original question and post the macro there, I can take a look at it.
don (rep: 1989) Oct 2, '16 at 6:27 pm