Help with VBA
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Help with VBA

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Dallas, TX
    Posts
    316
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello all,

    I write some sloppy VBA, that's why I'm here. I need help with a subroutine to check 6 random numbers for duplicates in the array. Here's what I have so far, but it doesn't work...please help.

    The numbers are in K5:P5

    Sub sgCheck()
    '*** check for duplicates
    If "L5" = "K5" Or "M5" = "K5" Or "N5" = "K5" Or "O5" = "K5" Or "P5" = "K5" Then
    sgRandom
    Else
    If "M5" = "L5" Or "N5" = "L5" Or "O5" = "L5" Or "P5" = "L5" Then
    sgRandom
    Else
    If "N5" = "M5" Or "O5" = "M5" Or "P5" = "M5" Then
    sgRandom
    Else
    If "O5" = "N5" Or "P5" = "N5" Then
    sgRandom
    Else
    If "P5" = "O5" Then
    sgRandom
    End If
    End If
    End If
    End If
    End If
    End Sub


    Steve


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Steve
    What will happen if a random number is found?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Dallas, TX
    Posts
    316
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, if a duplicate is found it runs the sgRandom routine until I have a set of 6 numbers (from 1 to 54) with no duplicates.

    Steve

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Steve

    Play around with this. I tested it.

    Sub sgCheck()
    Dim icol As Integer
    Dim iCnt As Integer

    For iCnt = 11 To 16
    For icol = 11 To 16
    If icol = iCnt Then Exit For
    If Cells(5, iCnt).Value = _
    Cells(5, icol).Value Then sgRandon
    Next
    Next
    End Sub


    Search VBA help for cells to understand the syntax. You do not use cell references in VBA the same way you do in formulas.
    Your code looks like it will work fine.
    Change all of your cell references like this.

    Change this:


    If "P5" = "O5" Then

    to this:

    If Range("P5").value = Range("O5").value Then

    Tom

    [ This Message was edited by: TsTom on 2002-04-04 21:41 ]

    [ This Message was edited by: TsTom on 2002-04-04 21:42 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Dallas, TX
    Posts
    316
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks Tom! I couldn't get the 1st routine you suggested to work, but adding the "Range" before the cell reference did the trick!

    Steve

User Tag List

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
  •  

 

 
DMCA.com