Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Random Number Generator

  1. #1
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    410
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Random Number Generator

    Hi

    I’m looking for a random number generator. If the customer has not supplied a Tax number/National insurance in Column A then a random number will be placed once the VBA is run, it will also highlight the whole row, in red.

    There can be no duplicates

    Its will be as TEMP12345678

    If a tax number has been provided it will skip that cell in column A an move onto the next blank cell, until it gets to last row with data in it.


    Column A

    1 777-55-8888
    2 777-55-8889
    3 TEMP12345678
    4 777-55-8890
    5 TEMP23456789
    6 777-55-8891
    7 777-55-8892

    In the example above Cells is rows 3 and 5 were empty so when the code was run a temp number was placed
    . I have looked at a few random number generators none do what I need.

    Last edited by Sharid; Apr 16th, 2018 at 11:38 AM.
    If I could code in VB, life wouldn't be such a pain in the A$$

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,643
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Random Number Generator

    Does this do what you want?
    Code:
    Sub RandIfEmpty()
    Const StartNum = 10000000 'Set the lowest number
    Const EndNum = 99999999   'Set the highest number
    Dim R As Range, c As Range, d As Object, X As String
    On Error Resume Next
    Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If R Is Nothing Then Exit Sub
    Set d = CreateObject("Scripting.dictionary")
    Application.ScreenUpdating = False
    For Each c In R
    Again:
        X = "TEMP" & Int((EndNum - StartNum + 1) * Rnd + StartNum)
        If Not d.exists(X) Then
            d.Add X, d.Count + 1
            With c
                .Value = X
                .EntireRow.Font.Color = vbRed
            End With
        Else
            GoTo Again
        End If
    Next c
    Columns("A").AutoFit
    Application.ScreenUpdating = True
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,398
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Random Number Generator

    Code:
    Public Sub FillInBlankNI()
    
    Dim lastRow As Long
    Dim thisRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For thisRow = 1 To lastRow
        If Cells(thisRow, "A").Value = "" Then
            Cells(thisRow, "A").Value = "TEMP" & Format$(WorksheetFunction.RandBetween(0, 99999999), "00000000")
            Cells(thisRow, "A").EntireRow.Font.Color = vbRed
        End If
    Next thisRow
    
    End Sub
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,087
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Random Number Generator

    Here is another macro that should work...
    Code:
    Sub FillBlanksWithRandomTempNumber() Dim RandNum As Long, Cell As Range, Blanks As Range, Nums As String For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks) Do RandNum = Application.RandBetween(10000000, 99999999) Loop While InStr(" " & Nums & " ", " " & RandNum & " ") Nums = Nums & " " & RandNum Cell.Value = "TEMP" & RandNum Cell.Font.Color = vbRed Next End Sub
    Last edited by Rick Rothstein; Apr 16th, 2018 at 12:08 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,087
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Random Number Generator

    Quote Originally Posted by wideboydixon View Post
    Code:
    Public Sub FillInBlankNI()
    
    Dim lastRow As Long
    Dim thisRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For thisRow = 1 To lastRow
        If Cells(thisRow, "A").Value = "" Then
            Cells(thisRow, "A").Value = "TEMP" & Format$(WorksheetFunction.RandBetween(0, 99999999), "00000000")
            Cells(thisRow, "A").EntireRow.Font.Color = vbRed
        End If
    Next thisRow
    
    End Sub
    While admittedly highly unlikely, your code would permit duplicate random numbers to be assigned.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular Sharid's Avatar
    Join Date
    Apr 2007
    Posts
    410
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Random Number Generator

    Thanks both of you, saved my life
    If I could code in VB, life wouldn't be such a pain in the A$$

  7. #7
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,398
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Random Number Generator

    Quote Originally Posted by Rick Rothstein View Post
    While admittedly highly unlikely, your code would permit duplicate random numbers to be assigned.
    I like to live dangerously.

    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,087
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Random Number Generator

    Quote Originally Posted by Sharid View Post
    Thanks both of you, saved my life
    It is hard to tell from the timestamp on your message whether you saw what I posted in Messages #3 and #4 yet. If you haven't, I would like to draw your attention to them.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular wideboydixon's Avatar
    Join Date
    Jun 2016
    Location
    Sheffield, UK
    Posts
    3,398
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Random Number Generator

    Quote Originally Posted by Rick Rothstein View Post
    It is hard to tell from the timestamp on your message whether you saw what I posted in Messages #3 and #4 yet. If you haven't, I would like to draw your attention to them.
    You missed the "entire row" requirement for the cell colour I think.

    Code:
    Cell.EntireRow.Font.Color = vbRed
    WBD
    Excel 2016 on Windows 10.
    Please use [code] tags when posting code.
    Please post data in proper tables - I suggest this tool.
    Remember to make a copy of your spreadsheet before running any macros that might change the data - macros can't be undone!

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,087
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Random Number Generator

    Quote Originally Posted by wideboydixon View Post
    You missed the "entire row" requirement for the cell colour I think.

    Code:
    Cell.EntireRow.Font.Color = vbRed
    Yes I did. Thanks for point that out. To make it easier for the OP, here is the corrected code...
    Code:
    Sub FillBlanksWithRandomTempNumber()
      Dim RandNum As Long, Cell As Range, Blanks As Range, Nums As String
      For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks)
        Do
          RandNum = Application.RandBetween(10000000, 99999999)
        Loop While InStr(" " & Nums & " ", " " & RandNum & " ")
        Nums = Nums & " " & RandNum
        Cell.Value = "TEMP" & RandNum
        Cell.EntireRow.Font.Color = vbRed
      Next
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •