VBA/Excel: Tracing precedents to another sheet not

Question

I recently started working with a friend's excel project, making macros. I was mostly doing this through the "Record Macro" function, as I am not knowledgeable in Visual Basic to code it myself, and there is not enough time to learn the language.

All was going smoothly until I learned that ctrl-[ would not work to trace a cell's precedents inside of a macro because it just selected the cell that the first precedent was linked to, and would not change for future precedents. I looked up how to code it in Visual Basic and ended up using "Selection.Precedents.Select" to trace the cell's precedents. Because the precedent I am trying to link to is on a different sheet (still in the same workbook) it returns "Run-time error '1004': No cells were found."

It looked like this:

Range("U5").Select
Selection.Precedents.Select

Because this did not work, I went to the "Trace Precedents" button on the Formula Auditing tab, and it was able to trace back to the precedent on the other sheet (ctrl-[ also worked to select the cell). I then tested a macro using a different cell as the precedent on the same sheet (so U5 was linked to V5, instead of the cell on the other sheet), and it was able to select the precedent cell.

I did some research and found a lot of answers that just said "Use Selection.Precedents.Select", which I did, and it didn't work.

Edit: I got it working, I had to do:

Worksheets("Sensitivity Table").Activate
Range("U5").Select
Range("U5").ShowPrecedents
ActiveCell.NavigateArrow True, 1

Probably not the most efficient way to do it, but as far as I know, it works. You just have to remove arrows at the end of the code.

Answer 1

The Precedents property of the range object only applies to ranges on the current worksheet. To get all the precedents, you would need to parse the formula.

See:

Charles Williams Answer

A typical approach is discussed in Colin Legg's Blog