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