MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Editing cell references w/ VBA

  Asked By: June    Date: Nov 27    Category: MS Office    Views: 1979

Currently, each cell within a certain range of a template
contains "=#REF!A1". I want to write a few lines of code so that when
I add this template onto an existing workbook, I can update all "#REF!"
to the name of a certain worksheet. How do I go about doing this?



6 Answers Found

Answer #1    Answered By: Jimmie Ramirez     Answered On: Nov 27

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

Answer #2    Answered By: Gilberto Thompson     Answered On: Nov 27

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?

Answer #3    Answered By: Abbas Hashmi     Answered On: Nov 27

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")"


Answer #4    Answered By: Jana Franklin     Answered On: Nov 27

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

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.

Answer #5    Answered By: Clay Cook     Answered On: Nov 27

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!",

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

Answer #6    Answered By: Josephine Gomez     Answered On: Nov 27

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

Didn't find what you were looking for? Find more on Editing cell references w/ VBA Or get search suggestion and latest updates.