Replace VLOOKUP formulas with a new formula

[toolman_dustin]

Attached is a spreadsheet with VBA code associated with sheet named "DOW10".
This is one of perhaps 30 spreadsheets that need to be modified.
The goal of the VBA code is to replace the VLOOKUP commands on all 30 sheets with a modified formula.

Everything works except at the very last instruction where the Cell formula is to be replaced with the NewFormula.
After more hours than I care to admit, I cannot determine why the NewFormula fails to overwrite the old.
Below and also at the top of the VBA code is a description of what is taking place.

My suspicion is the problem is related to a type-declaration mismatch, but I'm not positive.

The Prices sheet is supposed to pull data from a quote system feed. It will not work unless you have access to Interactive Data.
However, you do not have to have this data running in order to change the VLOOKUP formulas.

thank you for your help.

Dustin






' The purpose of this code is to hunt down occurances of VLOOKUP and edit them.
' I have included it with a worksheet titled DOW10.
' Areas colored in yellow perform VLOOKUP in the Range of A20:AD300 or AD614
' the range is inconsistent
'
' Areas colored in Green perform VLOOKUP in the Range of A5:AD12.
' All of the VLOOKUP formulas contain the "TRUE" "range_lookup".
'
' The goals is to change all the TRUEs to FALSE and make the VLOOKUP ranges consistent.
'
' Column S and W contain very long formulas with column W having 3 VLOOKUP formulas embedded.
'
' The code below reads a formula from a cell and then shifts characters out from
' left to right. As it is extracting characters it looks for the characters "VL"
' where it then creates a "corrected" formula.
'
' If the VLOOKUP range starts with "A5" it searches the top of the Prices sheet.
'
' If the VLOOKUP ranges starts with "A20" it modifies the range to A20:A500.
'
' The routine also adds the IFERROR prefix to the VLOOKUP and "Symbol Not Found"
' as a suffix.
'
' HERE's THE PROBLEM: EVERYTHING works right up to the point that I attempt to
' replace the cell formula with the NewFormula.
'
' As cell are modified they are painted purple.
'
'
' After many many hours of studying I cannot determine why the new formula will not
' replace the cell formula.
'
'

[Kaper]

Hi,
Wow, I've seen some pieces of easier to read code . Looks a bit like my old school times with Fortran.

OK. Now the helpfull part. Tough to spot, but finally I thing did it.
change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

in two places in your code.

[toolman_dustin]

Kaper, You are correct that did fix the problem. I was able fix 40 spreadsheets in about 6 minutes.

I expected this project to take a couple of hours to build. I then ran in to numerous inconsistent formulas as they were developed by different financial advisers over many months. I had to keep modifying the this code to compensate and it became more and more unreadable.

Thanks so much for finding the problem.

Dustin

[Kaper]

Glad it worked.

Sorry for may be too open comment on the code. You knew the story behind. I just saw the code and focused on debugging.
And seeing the number of GOTOs it reminded me early 80's and FORTRAN classes in my secondary school.