View Full Version : [SOLVED:] Automatic random code pattern
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
11-22-2013, 05:28 AM
Why VBA, why not just a formula such as
=CHOOSE(RANDBETWEEN(1,2),"TES","ARQ")&TEXT(RANDBETWEEN(1,145),"000")
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
11-22-2013, 07:01 AM
So now I am confused as to what the question is.
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
11-23-2013, 04:41 AM
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.
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
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
11-24-2013, 04:35 AM
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
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!
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
11-24-2013, 01:51 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.