-3

My Data is

Person     City    New City
A          P
B          Q
C          R

I want to randomly assign the New City to a person from the list of City (Column 2). However, New City should not be same as the one which is already assigned to the person.

I tried creating an Excel macro, however, I'm unable to get the Reference Library for the same. e.g. 

Dim RandomClass As New Random()
Dim RememberSet As New HashSet(Of Integer)

Dim RandomNumber As Integer

While RememberSet.Count < 5
    RandomNumber = RandomClass.Next(0, 10)
    If RememberSet.Add(RandomNumber) Then
        MsgBox (RandomNumber)
    End If
End While

gives a compile time error at first two lines. Can anyone please help? Or upload an excel macro which is using list or collections?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Hardik Patel
  • 79
  • 2
  • 6
  • 1
    Can you please clarify what language this actually is: It is tagged [tag:vba] and [tag:excel] and [tag:macros] yet in your comment to me how's answer you say "It's VB". They are not the same and the answers will vary. I have added collections for BOTH languages in my answer now, but please clarify. Also If possible, please consider closing some of your other open questions by selecting the best answer. Consider which answers have been useful and need an upvote. We need users to maintain their questions so that the site can be an effective tool for the next person with your problems. – RossC Sep 04 '14 at 11:07
  • Since the code has changed, this doesn't appear to me to be VBA it is VB, they are not the same. Things like `HashClass(Of Integer)` aren't part of VBA. I would pick a language and learn the syntax / google appropriately. – RossC Sep 04 '14 at 11:20
  • @RossC but the OP said he is writing in Excel.. :) it gives a syntax error because its not the correct VBA syntax...I will let you deal with this if you want ;) –  Sep 04 '14 at 11:21
  • @mehow too kind :| thanks! :P Yeah it's a VBA question but the code is in VB, they are different languages, you are speaking French to a German person here. – RossC Sep 04 '14 at 11:23
  • @RossC oh yeah yeah I am aware :) I opt out from helping help-vampires though :p –  Sep 04 '14 at 11:23
  • @RossC I think what has happened here is the OP found some VB(VB.NET) code and tried to use it within VBA and that is the source of the problem...I agree with you this is worthless spending any more time on. –  Sep 04 '14 at 11:30
  • 1
    @Hardik You have rewritten the question so that your previous attempt is no longer visible. Now the answers point to nothing. I'm am reverting your edit and updating the post. – Jan Doggen Sep 04 '14 at 12:16

1 Answers1

1

The issue is that you are using syntax for a problem. They are different languages, with different syntax. You are speaking a different language. Excel uses , Visual Basic for Applications i.e. Microsoft Applications. Adjust your code and syntax accordingly.

Assuming you are using VBA as per your question: You could use List Item or a Collection to do this. 

Dim varCollection As Collection

There is no List class that I know of in like there is in

In VB (as opposed to VBA) you can use:

Dim varCollection As New Microsoft.VisualBasic.Collection()
Community
  • 1
  • 1
RossC
  • 1,200
  • 2
  • 11
  • 24
  • Actually I want to use this in Excel Macro where I think VB is used. However, this is giving me compile time error (syntax error) and the reason for the same I think is it's not getting reference library. So I want to know which Reference Library should I mark to use collection objects ? – Hardik Patel Sep 04 '14 at 11:09
  • 1
    @HardikPatel A syntax error? Is there some code you are not sharing that is causing it? –  Sep 04 '14 at 11:11
  • 1
    I don't have any reference library for this and it works fine. Dictionary needs scripting.runtime but collection is part of VBA. Try `Dim myCollection As VBA.Collection` **WHAT** syntax error? We need more details, just saying an error is no good to any of us. – RossC Sep 04 '14 at 11:12