Automatic random code pattern

[elsg]

hellow
I need to create VBA code to create a random code automatic (based on default -> TES001 ... TES145 and ARQ001 ... ARQ145 etc.).


I have a tab "Tb_Rnd" which would crado codes via VBA.


But I need not repeat codes.


Aupha-Random-Automatic-Used


TES001


ARQ001

[Bob Phillips]

Why VBA, why not just a formula such as

=CHOOSE(RANDBETWEEN(1,2),"TES","ARQ")&TEXT(RANDBETWEEN(1,145),"000")

[elsg]

interesting, but I need a way to compare "Used" tab with the "Tb_Rnd"


Used happens that tab enter an alphanumeric based on "Tb_Rnd" tab but can not repeat.
Tab "Used"


ID's
Descr


TES001
Plano de teste


TES002
Fluxo de integração


TES003
Acompanhamento de execução


ARQ004
Documento de arquitetura


ARQ005
DAS


ARQ006
DASW


TES001
<-- it Code TES001 can not get into A10, becouse repeat in "Tb_Rnd"


Tab "Tb_Rnd"


Rand


TES001


TES0061


ARQ0248


ARQ0168


ARQ0275


TES0883


ARQ0624


ARQ0175

[Bob Phillips]

So now I am confused as to what the question is.

[elsg]

I will use your formula in the "Tb_Rnd" tab and the "Used" tab will be in accordance with a click of a button station, but it can only be called the ID is not repeated in the "Used" tab

[Bob Phillips]

Sorry, but I just don't understand.

The phrase ... Used happens that tab enter an alphanumeric based on "Tb_Rnd" tab but can not repeat... is beyoind my comprehension.

And this ... and the "Used" tab will be in accordance with a click of a button station, but it can only be called the ID is not repeated in the "Used" tab ... also is beyond me.

[elsg]

i'm try this, but not apear nothing in active cell

Sub Not_It_Work() Worksheets("Used").Activate
With ActiveCell
.Formula = "=INDEX(Tb_Rnd!$A$2:$A$1000,RANDBETWEEN(0,COUNTA(Tb_Rnd!$A$2:$A$1000)))"
.Value = .Range("A2").Value
End With
End Sub

[elsg]

hellow
Now I need to find the value in the active cell and delete this value "Tb_Rnd" tab
How do I do that?

Sub Now_It_Work() Worksheets("Used").Activate
With ActiveCell
.Formula = "=INDEX(Tb_Rnd!$A$2:$A$1000,RANDBETWEEN(0,COUNTA(Tb_Rnd!$A$2:$A$1000)))"
.Value = .Value '<- this line was wrong, but I fix
End With
'Now I need to find the value in the active cell and delete this value "Tb_Rnd" tab
'How do I do that?
'Thank you!
End Sub
Thank you!

[mancubus]

Now I need to find the value in the active cell and delete this value "Tb_Rnd" tab


hi.

try this.



Sub find_n_del()


Dim rng2del As Range

On Error Resume Next
Set rng2del = Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlWhole, xlByRows, xlNext).Clear 'match entire cell contents
'Set rng2del = Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlPart, xlByRows, xlNext).Clear 'partial match
If Not rng2del Is Nothing Then
rng2del.Clear
Else
MsgBox "Searched value not found!", vbOKOnly, "Not Found"
End If
On Error GoTo 0


End Sub

[elsg]

Funny, the code reports by a msgbox that did not match, but in "Tb_Rnd" tab has been deleted by the code.


how to fix, this MsgBox?


Thank you!

[elsg]

I try this

Sub find_n_del() Dim rng2del As Range
On Error Resume Next
Set rng2del = Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlWhole, xlByRows, xlNext).Clear 'match entire cell contents
'Set rng2del = Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlPart, xlByRows, xlNext).Clear 'partial match
If Not rng2del Is Nothing Then
rng2del.Clear
On Error GoTo 0
End If
With Worksheets("Tb_Rnd").Activate
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
Columns("B:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End With
Worksheets("Used").Activate
End Sub
Thank you!!!

[mancubus]

ooppps.

sory for that.

my first code was


Sub find_n_del()
Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlWhole, xlByRows, xlNext).Clear 'match entire cell contents
'Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlPart, xlByRows, xlNext).Clear 'partial match
End Sub


thinking that ActiveCell's value always exists in "Tb_Rnd".

but what if there is not a match... so i added error handling. but i forgot to delete the "Clear"!



correct procedure must be like this:


Sub find_n_del()


Dim rng2del As Range

On Error Resume Next
Set rng2del = Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlWhole, xlByRows, xlNext) 'match entire cell contents
'Set rng2del = Worksheets("Tb_Rnd").Cells.Find(ActiveCell.Value, , , xlPart, xlByRows, xlNext) 'partial match
If Not rng2del Is Nothing Then
rng2del.Clear
Else
MsgBox "Searched value not found!", vbOKOnly, "Not Found"
End If
On Error GoTo 0

End Sub