Macro to replace text with incremental number

[isabelle r]

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 last row.

The number of "Not Listed" entries varies in number and rows 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

[mdmackillop]

Sub NotListed()
Dim c As Range
Dim FA As String
Dim i As Long
With Columns(3)
Set c = .Find(What:="Not Listed", LookIn:=xlFormulas)
        If Not c Is Nothing Then
            FA = c.Address
               Do
               i = i + 1
               c.Formula = "COMPANY" & Format(i, "000")
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
        End With
End Sub

[mana]

Option Explicit

Sub test()
    Dim c As Range
    Dim n As Long
    
    With Range("C1", Range("C1").End(xlDown))
        .AutoFilter
        .AutoFilter 1, "Not Listed"
        For Each c In .SpecialCells(xlCellTypeVisible)
            If n > 0 Then
                c.Value = "COMPANY" & Format(n, "000")
            End If
            n = n + 1
        Next
        .AutoFilter
    End With

End Sub

[mdmackillop]

Hi Mana
Why the two Autofilters?
Regards
MD

[mana]

Probably, but I always use two autofilters just in case.

[mdmackillop]

Thanks

[isabelle r]

Thank you both.