Formula Precedents

  • Thread starter Brendan Vassallo
  • Start date
B

Brendan Vassallo

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
 
K

Ken Johnson

Hi Brendan,

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

Ken Johnson
 
B

Brendan Vassallo

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
 
Top