Moving an array formula to VBA only, with memory?

Good morning,
I have this array formula that's listed down a column C2:C69.

Please Login or Register  to view this content.

It checks a two different columns for matching references, and if both match, then it returns the VLOOKUP value for the one that matches both references.

Currently, obviously, the formula runs in each of the 68 cells down the column. C2 starts with the above formula and is repeated down until it stops in C69.

It does a great job. However, is there a way to run this automatically in VBA when there's matching references instead?

For example, the array formula at cell C15 has a value that's returned blank because currently there's no matching references for the VLOOKUP. However, I'd like to be able to manually enter a value, say "123456" into cell C15. While C15 = 123456, if ever there's a time where there is a matching reference that would be returned in C15 instead, I want it to overwrite what's currently written into cell C15. Overwriting indiscriminately is not really ideal, so in addition, is it possible in VBA for it to "remember" the manually entered value and store it, then if the value from the VLOOKUP is blank, it'll return the original manually entered value in its place from storage? Does that make sense?


I do have simpler solution to accomplish the same thing, but the way we'd use the file, it becomes kind of an issue. There'd be a lot of very specific protections we'd have to make to ensure folks don't break the file. None of them know how to open the VBA editor to mess that up.

It's for a dock schedule. I want the cells to automatically return a Trailer # that's recorded in Workbook2, but sometimes there's trailers that we place at locations that are not being recorded and instead need entered manually, but should be overwritten if there's ever something assigned to that location instead.

Could you not put your manual entry into a different column? E.g. put "123456" into cell Z15 and change your formula:

Please Login or Register  to view this content.

WBD