Formula Precedents

[Guest]

Hi All,

I am writing a macro where I want to change the range of cells referenced in
a formula to a new range. I was planning on trying to somehow get a string
variable (called strRangeO) to be equal to the precedent cells the formula
refers too and replace it with a new string variable (called strRangeN).
strRangeN would be equal to the new selected range address as per below:

strRangeN = Range(ActiveCell,ActiveCell.Offset(0,3)).Address(xlA1)

My problem is that I can't seem to find the function that will let me
populate strRangeO with the current address of the range the fromula refers
too.

Can anyone help?

Brendan

 

[Ken Johnson]

Hi Brendan,

If the formula is in A1, Range("A1").Precedents.Address returns the
address of the precedents.

Ken Johnson

 

[Guest]

Ken,

I tried the code below and got an error stating that "no cells were found".
I forgot to mention that the formula and the precedent cells are on different
sheets of the spreadsheet.

Can you suggest an amendment to your code below that may help?

Brendan