Replace text in column with a unique number

Hi all,

I have a report which lists a number of company details. Some of these companies are not matched in the system, and instead of showing the company name in col. C, the report just displays "Not Listed".

As I use the report for a number of lookups, I'd need to change each instance of the "Not Listed" text by a unique number, which would allow me to obtain the unique reference to help me with my lookup.

For instance, say the first instance of this is in C2. "Not Listed" would become "COMPANY001".
Then in the next instance, displayed say in C15, "Not Listed" would become "COMPANY002".
And so on for each instance of "Not Listed" until the end of the list.

The number of "Not Listed" entries varies in number and row from week to week, but is always in col. C.

Is there a simple way to do this in VBA?

Thanks for any advice

-Isa

(This was mistakenly posted in the functions forum. I've closed the other post)