List all formulas in active workbook

[jomili]

I have the macro below, which I cobbled together many years ago and use often, to list every formula in the workbook, with the address of said formula. I've noticed, belatedly, that it's producing an error. In the attachment I have sheets "Unique Values", which contains formulas and "Data", which has no formulas, just data. The sheet "Formulalist" is added by the macro, and lists all the formulas in the workbook. the error it's creating is for "Data" it's listing the same formulas as "Unique Values", and I can't figure out why. The macro is in the workbook and pasted below. I'd appreciate any help I can get on this.

Please Login or Register  to view this content.

[Fluff13]

try

Please Login or Register  to view this content.

[jomili]

Fluff13, you're awesome. I never thought about putting a check to see if there WAS something in the NewRng. I also appreciate how you simplified the values being placed. I'm forever in your debt.

[Fluff13]

Glad to help & thanks for the feedback.
For reference, you can speed things up by using this

Please Login or Register  to view this content.

[jomili]

You're using a new term for me, "ary". Is that short for Array, and if so should I declare it as a variable?

[Fluff13]

Yes, it's just my way of naming an array & should be declared as

Please Login or Register  to view this content.

[jomili]

Getting Subscript out of range on

Please Login or Register  to view this content.

[Fluff13]

Missed a bit

Please Login or Register  to view this content.

[jomili]

That's funny; I had thought the same thing, AND put in the same "I=0", but in the wrong place.