VBA code to copy specifics of errored formulas to new worksheet

[Guest]

Using XL 2003 & 97

Am able to select cells with "errored" formulas but need help to identify
then copy
certain aspects of the cell information to another worksheet.

The following VBA code locates the cells that I need:

Selection.SpecialCells(xlCellTypeFormulas, 16).Select

What I would like to do is paste information about the selected cells to new
sheet.

The column headings on new sheet (for each errored cell selected) would be:

Address Row Column Display (on screen) Formula (in each selected
cell)



TIA Dennis

 

[Frank Kabel]

Hi
Selection.SpecialCells(xlCellTypeFormulas, 16).copy
worksheets("sheet2").paste

 

[Guest]

Thanks Frank.

My question must have been unclear!

Copy/paste of noncontiguous cells does not work in the first place.
Secondly, it will not copy the attributes about which I asked.

Let us assume that I have two cells on my sheet1 that have errors in the
formula:

In B2 I have a #REF error
In D19 I Have a #NAME error

I would like have displayed on sheet2 show:

Address Row Column Display Formula
B2 2 B (or 2) #REF =A1+#REF
D19 19 D (or 4) #NAME =B4+X

If I knew the syntax for addressing the variable names to copy to sheet2,
which must come from some loop or sequential processing, I could at least get
started.

In short, as each selected cell is evauated in the loop, I need to address
its;
Address; Which row and column; What is it currently displaying on the screen;
and what is the current formula. Then hold those variables for later
pasting to sheet2.

Then I need to copy each set of variables onto Sheet2.

If the answer is too complicated for the forum then just let me know.

Otherwise, consider pointing out some similar code somewhere in the MVP's
master databases that I could adapt.

I am more than willing to do the work, I just need a foundation that works.

Dennis