Long time lurker, first time poster - I have searched EVERYWHERE.
Anyways, the title is
just the tip of the iceberg so I will do my best to explain - if I can clear anything up for you further, please ask.
To prelude, it should be noted that I deleted a LOT of information/code, but I believe all of the relevant items are still
there. Second, I only taught myself coding about a month ago so please bear with me.
On my attached workbook, I
pull information in from an external source to populate Sheet 1 (Rate_Link). Upon pulling in the information, Column G is
inserted to create a Concatenation column out of the initial pulled data. Next, data selected by the users from the boxes in
Sheets("Main") is sourced to cells in Sheet 9 (Population_Key), then concatenated into Cell $H$1. Using the Concatenation in
$H$1, I use a macro attached to a button to apply a formula array of multiple-vlookups [(because 2 values exist for 1
concatenation) from VBA Inserted Column G & Column H on Sheet 1 (Rate_Link)], then the button performs a 'paste special' of
sorts to remove the "empty" (blank valued formulas) cells, leaving merely the values "A,B" in Column L. The next step in my
process would be to apply a dynamic name to the results in Column L and use it in a Combo Box.
Here's my problem:
When I open the workbook, the inserting of column G shifts the multiple vlookup array 1-3 columns to the right to make the
array, usually $I$1:$J$? instead of $G$1:$H$?.
NOTE: I deleted the Insert Column G on Workbook Open, because I was
torn between leaving the formatting/document proper/'working' and keeping the document authentic (with the on open code).
I know this is a sub-question, but it is directly related to my individual cause and the exact same set of cells:
Further, I wanted to NOT have the code on a button (though it is present and currently working) but on a worksheet change
event with Sheet 11, in the event that all 4 criteria are satisfied (no cell = 0). I tried with an ELSE Run or Call Macro
(RouteListMacro), but it didn't work (even though the command button with this macro does) so I established a Do Loop Until
on the Worksheet Change, but it does not register. I think this issue might be directly related to the Array shift I
described above, but I am uncertain. I say this because I initially had it working, but had closed the workbook then reopened
and it was no longer working.
Thank you in advance for you help and your contribution to my future knowledge.