VBA Code to update last Max value if source cell has formula linking to another cell

Excel 2010 - VBA Code to update last Max value if source cell has formula linking to another cell

Can someone please help?

The following VBA code enters in cell C2 the max value ever entered in cell A2 which is a dynamic changing cell. In other words, cell A2 may have the value 2, then at another time it will the value 6, then 11... etc. The VBA code will write (for example) 11 in cell C2 if 11 was the last highest value used in cell A2.

The code works fine as long as the dynamic value is entered manually in cell A2. However, if cell A2 has a formula pointing to another cell (in this example, pointing to cell A6) to retrieve the dynamic value then the VBA code will not update the max value in cell C2.

How can the following VBA code be made to work with a formula in cell A2? NOTE: the code will work with the formula if I double click in the cell A2 and then press Enter. This will update the cell C2 correctly.

Please Login or Register  to view this content.

Please see attached file.

TMS, you guessed it right. Cell A2 has a bit more complex formula. Cell A6 is not actually a manual formula in my real workbook, it's an even more complex formula that merges several changing cells.

Therefore, I need the code to grab the last highest number that passes through cell A2 without me having to manually update cell A2.

I have also tried with Private Sub Worksheet_Calculate() to no avail. I guess I have to think of a different approach.

TMS,

It works flawlessly.

Thanks a billion.

TMS,

The worksheet uses VBA code that evaluates certain results and matches them to a grid while spiting out those values in some columns, and while it does that it also marks with an X the row being processed. Then there are some array formulas that check (doublecheck) that nothing was skipped, for example this one (which is in place in the workbook, but it would have been the formula in A2 in the example here provided): =MAX(IF($S$1:$S$3690="X",ROW($S$1:$S$3690))). This array formula spits out which row number is being processed. So, with your code, if we have 2,000 records it will tell me which record was the highest that was ever processed (based on the array formula) - this allows me to visually compare the total records to process vs the highest that was processed.

Thanks for everything.