PDA

View Full Version : [SOLVED] Macro to replace text with incremental number



isabelle r
05-22-2017, 05:31 AM
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
05-22-2017, 06:06 AM
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
05-22-2017, 06:54 AM
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
05-22-2017, 07:26 AM
Hi Mana
Why the two Autofilters?
Regards
MD

mana
05-22-2017, 08:02 AM
Probably, but I always use two autofilters just in case.

mdmackillop
05-22-2017, 08:09 AM
Thanks

isabelle r
05-23-2017, 01:31 AM
Thank you both.