CODEWITHSUNDEEP
vbaexcel
Adem
Adem

Reputation: 45

IF cells begin with AND Create random number

I want to make a sub, which determines if the cells in the 12th column starts with 262015. If it does start with this, it should create a new random 8-digit number starting with "18" and then 6 randomly created unique digits.

My code does not seem to figure out if the cell starts with 262015, and I have not been able to find help on creating the 8-digit number with these requirements.

Hope you can help me!

Sub Opgave8()

For i = 2 To 18288
    If Left(Worksheets("arab").Cells(i, 12), 6) = "262015" Then
        Worksheets("arab").Cells(i, 3) = "18" & studyid(6)
    End If
Next i

Function UniqueRandDigits(x As Long) As String
Dim i As Long
Dim n As Integer
Dim s As String
Do
n = Int(Rnd() * 10)
If InStr(s, n) = 0 Then
s = s & n
i = i + 1
End If
Loop Until i = x + 1

UniqueRandDigits = s
End Function

End Sub

Upvotes: 0

Views: 69

Answers (2)

Harassed Dad
Harassed Dad

Reputation: 4714

For i = 2 To 18288
    If Left(Worksheets("Base").Cells(i, 12), 6) = "262015" Then
        Worksheets("Base").Cells(i, 3) = "18" & Randdigits(6)
    End If
Next i

Function RandDigits(x As Long) As String
Dim i As Long
Dim s As String
For i = 1 To x
    s = s & Int(Rnd() * 10)
Next i
RandDigits = s
End Function

EDIT: here's one where all digits are different

Function UniqueRandDigits(x As Long) As String
Dim i As Long
Dim n As Integer
Dim s As String
Do
n = Int(Rnd() * 10)
If InStr(s, n) = 0 Then
s = s & n
i = i + 1
End If
Loop Until i = x + 1

UniqueRandDigits = s
End Function

EDIT2: And here is one that forces all numbers to be different

dim n as  string
dim ok as boolean
For i = 2 To 18288
    If Left(Worksheets("Base").Cells(i, 12), 6) = "262015" Then
        ok = false
        do
        n = UniqueRandDigits(6)
            If Application.WorksheetFunction.CountIf(Worksheets("Base").Range("L2:L18288"), n) = 0 Then
                Worksheets("Base").Cells(i, 3) = "18" & n
                ok = true
            end if
        loop until ok
    End If
Next i

Upvotes: 3

Shai Rado
Shai Rado

Reputation: 33692

Using Left function, you need to specify the String, then the number of characters from the left, and then you check if it's equal to "262015".

Try the code below:

For i = 2 To 18288
    If Left(Worksheets("Base").Cells(i, 12), 6) = "262015" Then
        Worksheets("Base").Cells(i, 3) = "XXX"
    End If
Next i

Upvotes: 1

Related Questions