check distinct value VBA

Question

I have a do loop which I want to keep looping until all 9 or so values are unique.

Do
     m1 = createManager
     m2 = createManager
     m3 = createManager
     m4 = createManager
     m5 = createManager
     m6 = createManager
     m7 = createManager
     m8 = createManager
     m9 = createManager
Loop Until m1 <> m2 <> m3 <> m4 <> m5 <> m6 <> m7 <> m8 <>m9

These 9 values are created by a function which gets a random indexed value from an array.

Function createManager() As Variant

    'Creates a random manager between the ages of 30 & 60
    Do
        Randomize Timer()
        pos = Int((UBound(arr2)) * Rnd + 1)
        age = arr2(pos)
    Loop Until age > 30 And age < 65

    'Assigns the appropiate mangerID to the manager
    managerID = arr(pos)

    createManager = managerID

End Function

What I am having issue with is the loop until part. m1 <> m2 <> m3 <> m4 <> m5 <> m6 <> m7 <> m8 <>m9 is what i have as a placeholder but is there an easy way to write this without a long IF AND statement?

UPDATE: Currently exploring collections and seeing whether this will provide by with what I am after...

Have your function return a collection or dictionary object, then you don't need to call it in a loop, you simply assign the `m1 = coll(1): m2 = coll(2): m3 = coll(3)...`. — David Zemens, Apr 27 '14 at 01:28
Where is `arr` and `arr2` defined in your Function? This function raises an error unless you have defiend these elsewhere, in which case, please provide the minimum amount of code needed for others to replicate your problem. — David Zemens, Apr 27 '14 at 01:30
Also, consider using `Application.WorksheetFunction.RandBetween(30, 65)` to return a random value between 30 and 65, instead of looping some array... — David Zemens, Apr 27 '14 at 01:33
I agree. After exploring the collection route a little more, have decided that that is the best way to go. BYW: arr2 and arr are declared as public arrays. — Chris, Apr 27 '14 at 03:33
Also, in regards to the randbetween suggestion, there may not be an age value within my array that would match. To explain further, say I have a bunch of employees with different ID's and ages. I alsoo want to make and age appropriate one the manager of them all - ie a 17 year old should not be managing a 37 year old. If I do randbetween and none of my employees are of that age then I won't be able to assign them a manager. — Chris, Apr 27 '14 at 03:37
Well all of that would be readily apparent, had you provided the minimum amount of code needed for others to replicate your problem and assist you. I will post an example function which may (or may not) help you. Good luck. — David Zemens, Apr 27 '14 at 03:41

score 0 · Answer 1 · answered Apr 27 '14 at 03:44

0

Here is a function that will return a specified number of manager ages given the start and end age (you use 30 and 65, respectively).

This may or may not help you. Your comments indicate there is a lot more going on that you put in your question. The public variables which are not scoped in your code, I have no idea what you're doing or trying to do with them, etc. and because of this, it really isn't very clear what your actual problem is, or how we might be able to assist.

Good luck.

Sub Test()
Dim m as Variant

'Create 9 managers between 30 and 65
m = createManager(30, 65, 9)

End Sub

Function createManager(startAge As Integer, endAge As Integer, Optional numManagers As Integer = 1) As Variant
Dim dict As Object
If numManagers > (endAge - startAge) Then 
    createManager = CVerr(9)
    GoTo EarlyExit
End If

Set dict = CreateObject("Scripting.Dictionary")

Do
    dict(Application.WorksheetFunction.RandBetween(startAge, endAge)) = ""

Loop Until dict.Count = numManagers

createManager = dict.Keys()
Set dict = Nothing
EarlyExit:
End Function

answered Apr 27 '14 at 03:44

David Zemens

53,033
11
81
130

Firstly David, I want to say thanks for all your help. Initially I though that there may be an easy way to solve a small problem without going into too much detail, but I see now that is not the case. What I am trying to create a massive dummy data set that mirrors a hospital.... – Chris Apr 27 '14 at 03:58
...This part of the code is trying to create a hierarchical structure for about 50,000 employees. The array arr ends up taking the EmpID's of all my employees within a subdepartment. The array arr2 takes their corresponding ages. (I didn't use a multidimensional array I needed it to by dynamic in size as each subdepartment can differ in size but had to Redim Preserve it later to cut down on blank entries). I have already create quite a bit of data (ages, addresses, start and end dates, sex, etc) so am trying to leverage of that. So sorry for not being clear and thanks for your help - Chris – Chris Apr 27 '14 at 03:59
1

Why don't you just use: http://www.generatedata.com/ (or similar) to create dummy data? – David Zemens Apr 27 '14 at 14:11
Also, unfortunately if you can't provide enough detail, your question is not really a good one: either it is too localized to be of any practical use for anyone else, anywhere, ever, or you simply have not provided enough good information and detail for anyone to be able to help you. In either case, you're unlikely to get a response. Good luck on your problem. I advise: next time someone asks you to show your code or provide more detail, you should revise your question to include that information, rather than just talking *about* it in generalities. Good luck, but you've exhausted my patience – David Zemens Apr 27 '14 at 14:14
Fair enough. Point taken. – Chris Apr 28 '14 at 19:07

score 0 · Answer 2 · answered Apr 27 '14 at 03:45

Using a collection seems like the best way to go as I cannot add a non distinct key into a collection. In this case the variable 'control' the number of managers that I needed for all my employees in the department to have a span and control of 18. ie, no manager could have more than 18 employees beneath them.

Do
    Call createManager
    on error resume next
    managercollection.Add managerID, CStr(managerID)
Loop Until managercollection.count = CInt(control)

The procedure that creates the manager

Sub createManager()

    'Creates a random manager between the ages of 30 & 60
    Do
        Randomize Timer()
        pos = Int((UBound(arr2)) * Rnd + 1)
        age = arr2(pos)
    Loop Until age > 30 And age < 65

    'Assigns the appropiate mangerID to the manager
    managerID = arr(pos)

End Sub

score 0 · Answer 3 · edited May 23 '17 at 12:15

0

You could use a Scripting.Dictionary, which provides benefits over a collection:

Dim dict As New Scripting.Dictionary
Do While dict.Count < 9
    dict(createManager) = 1 'arbitrary value
Loop

edited May 23 '17 at 12:15

Community

1
1

answered Nov 29 '15 at 14:02

Zev Spitz

13,950
6
64
136

check distinct value VBA

3 Answers3