The formula in a cell  is simply a text string. You can retrieve it from the
cell, test it for a value in the normal way, and put it back into the cell.
I'm not sure which part of the code you are needing help in.

Perhaps you could post the code you've written so far and are having trouble
with.

It's not so much the code itself I was having
difficulty with as much as replacing A with B in the formula. Since
the formula is a text string then, could I use:

.Cells(i,j) = "=Replace("#Ref",1, 10, "Autofilter")"

I tried this before but it would come up with an error on this line
unless something else was causing it to falter. I will double check
on my part. But does that seem reasonable?

I am not at all entirely sure if my reply to your post went through,
but again, thank you Dave. It's not so much the code I had trouble
with but the idea behind it since I had not written any code yet.
But thank you, I think, from what you are saying,I would be able to
use something along the lines of

.Cells(i,j) = "=Replace("A", 1,1,"B")"

correct?

Firstly, you "should" refer to the formula in a cell  by .Cells(i,j).Formula
You'll possibly get away without it, but you should be explicit in case
Excel has trouble deciding what you mean.

Secondly, your string to the right of the equals has quotes within the
quotes. If you want quotes as literals in a string you need to put two of
them at each place.

Thirdly, I think you'll find that #REF is fully in capitals.

But, most importantly, you are putting into the cell (or trying to) the
formula =Replace("#Ref",1, 10, "Autofilter"). You are not replacing the
formula with a minor variation, you are replacing it with a replace
statement.

My statement that does it - for A1 only - is

Cells(1, 1).Formula = Replace(Cells(1, 1).Formula, "#REF", "Autofilter")

You'd need to change the 1,1 back and put the dot before both of the Cells
calls, but that's pretty much what you'd want.

I'm a bit nonplussed with some of the discussion on this one so far.
Suppose the 'certain range' is A1:B10 of the active worksheet,
further, suppose the 'certain worksheet' name is 'MySheetName', then I
recorded and simplified to:

Range("A1:B10").Replace What:="#REF!", Replacement:="MySheetName!",
LookAt:=xlPart

which worked and properly fetched data from the other worksheet and
used it in a simple formula.. but have I completely missed something?!

No you haven't missed anything. Elegant!! (Hits self in forehead for not
thinking of it first.)