Search code examples
excelformulas

Excel Find and Replace with Formulas - No VBA

I am curious if I can exploit the "find and replace" dialogue in Excel to perform the following task:

Excel formula in cell B1:

=myVBAFunction(A1)

Needs to change to 

=myVBAFunction(A1)+F1

This is easy with Find And Replace as I simply find "myVBAFunction(A1)" and replace with "myVBAFunction(A1)+F1". However I need to repeat this process for B2 which references A2 and F2. Then for B3 and so on.

Many people suggest dragging down the formula which is great, or I could adjust my function to incorporate the extra functionality. No need for Stackoverflow...

Here's the catch I can't use VBA and the dragging down won't work as I'm relying on the less-tech savvy user doing this in a failsafe way. The find and replace method would be the most reliable.

The user I'm instructing could do find and replace (a real example has multiple sheets) but I can't rely on them to find the cell and drag down the formula.

Yes I could email a new version but I'm curious if this can actually be done in a find and replace style way (or other equally simple)?

Solution

  • So I'm answering my own question - INDIRECT has saved the day.

    Quest - replace contents of A1 with

    =myVBAFunction(B1)

    with

    =myVBAFunction(B1)+F1

    Without using VBA, or dragging down the formula so that the method results with A1000 referencing B1000 and F1000 etc.

    You can use Find & Replace like this

    REPLACE with

    =myVBAFunction(INDIRECT("B" & ROW())) + INDIRECT("F" & ROW())

    Using INDIRECT and ROW allows the same replace text for every cell, but the referencing to work post replace.