Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Question Unanswered: Help Improve this process, or show me how to avoid duplicates in 5 random numbers?

    Hi guys,

    I'm a VBA user, and my VB knowledge is very limited so forgive me if I come back a few times for clarification.

    I would normally be using an access database and wouldn't have a problem otherwise but having to work with what I've been given at the moment I'm a little stuck; I have an excel spreadsheet containing 5 columns and 100 rows. Each Column should have some information in, but if column E is empty, I need to chase for the relevant information.

    I currently have something that begins an email, keeps randomly selecting a row until it comes across one where column E is empty, and then it prints the information from columns A, B, C & D into the email.

    It does this 5 times and then finishes building the email.

    My problem is that by randomly selecting 5 lines this way, it could potentially pick the same 5 lines in each email. Is there a better way of doing this whilst staying in Excel? I've stripped down the code to the essential bits here;


    Sub CustomerProgramNotes()

    Dim MacroRowCount
    Dim MacroSelectedRow
    Dim MacroSelectedCell
    Dim MacroBlankCellCount

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Dim MacroRandomRow
    Dim MacroRandomRowCount

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = ""

    MacroRandomRowCount = 1

    Do Until MacroRandomRowCount = 6

    Dim RandomRowIsBlank
    RandomRowIsBlank = "No"

    Do Until RandomRowIsBlank = "Yes"
    MacroRandomRow = Int(Rnd * ((MacroRowCount - 1) - 2) + 2)

    MacroSelectedCell = "D" & MacroRandomRow

    If Len(Range(MacroSelectedCell).Value) < 1 Then

    RandomRowIsBlank = "Yes"

    Else

    RandomRowIsBlank = "No"

    End If

    Loop

    MacroSelectedCell = "A" & MacroRandomRow
    strbody = strbody & " Customer: " & Range(MacroSelectedCell).Value & vbCrLf

    MacroSelectedCell = "B" & MacroRandomRow
    strbody = strbody & " Type: " & Range(MacroSelectedCell).Value & vbCrLf

    MacroSelectedCell = "C" & MacroRandomRow
    strbody = strbody & " Scanner: " & Range(MacroSelectedCell).Value & vbCrLf

    MacroSelectedCell = "D" & MacroRandomRow
    strbody = strbody & " Notes: " & Range(MacroSelectedCell).Value & vbCrLf

    MacroRandomRowCount = MacroRandomRowCount + 1
    MacroRowCountx = MacroRowCountx + 1

    Loop

    On Error Resume Next

    With OutMail
    .To = """
    .CC = ""
    .BCC = ""
    .Subject = "Customer Software Requirements"
    .Body = strbody
    .Display

    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    End Sub
    Last edited by christyxo; 06-08-11 at 11:15.

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    I should also have said that the only solution that I have so far come up with was to create 5 temp values.

    Generate a random and if it's blank call it Temp1
    Generate a random and if it's blank call it Temp2 but if it's the same as Temp1, do it again.
    Generate a random and if it's blank call it Temp3 but if it's the same as Temp1 or Temp2, do it again.
    etc...

    clearly that's going to become tedious

Posting Permissions

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