VBA correction

[Jan Kalop]

I have VBA formula which is working perfectly, but only the results are all in one column and I would like them to appear in one specific row.


"Sub TwentyfiveRandOf25ONLY6()
Dim anArray(1 To 28) As Long
Dim i As Long
Dim randIndex As Long, temp As Long
For i = 1 To 28
anArray(i) = i
Next i
For i = 1 To 28
Randomize
Do
randIndex = (Rnd() * 28) + 1
Loop Until randIndex <= 28
temp = anArray(randIndex)
anArray(randIndex) = anArray(i)
anArray(i) = temp
Next i
Range("ck5:ck11").Value = Application.Transpose(anArray)
End Sub"


When I change the array from ck5:ck11 to for sample to CK5:CR5 the formula do not work. What have to be change to get the result in row?

 

[Domenic]

To refer to worksheet cells from the active sheet...

  MaxNumber = Range("A2").Value
  HowManyRands = Range("B2").Value

Change the cell references accordingly.

 

[Kenneth Hobson]

There is nothing wrong with your English.

It is easy to add input parameters to any Sub. You can then add a Sub to pass the two values.

Even so, maybe you would want some other features:
1. If either of the 2 cell values change, execute the code. This is called being dynamic routine.
2. You may want a 3rd input parameter to set the first cell location.
3. Sort the data.

There is another issue that you may want to consider if you do multiple runs. That is to clear the last runs range of values first. You have to decide, clear from start cell and to right, or start cell and down.

I have a file that does all of this. You will find that there are common things like this requested. There are many ways to accomplish the same goals. We just need to know what those goals are.

 

[Jan Kalop]

To refer to worksheet cells from the active sheet...

  MaxNumber = Range("A2").Value
  HowManyRands = Range("B2").Value

Change the cell references accordingly.

Thank you.....I have done some changes but VBA not working


Sub GetRandomNumbers()
Dim i As Long, randIndex As Long, Temp As Long, anArray As Variant
Dim MaxNumber As Long, HowManyRands As Long
MaxNumber = Range("A2").Value
HowManyRands = Range("B2").Value
anArray = Evaluate("COLUMN(1:" & MaxNumber & ")")
Randomize
For i = MaxNumber To 1 Step -1
randIndex = Int(i * RND + 1)
Temp = anArray(randIndex)
anArray(randIndex) = anArray(i)
anArray(i) = Temp
Next i
Range("c5").Resize(, HowManyRands).Value = anArray
End Sub


Please could you make corrections?

 

[Kenneth Hobson]

"Not working" seldom helps us help. Post #13's code "works" fine. Maybe you did not notice that Rick changed the starting cell to C5?

Please paste code between code tags to keep the tab structure. Click the # icon on the toolbar to insert the tags.

 

[Jan Kalop]

"Not working" seldom helps us help. Post #13's code "works" fine. Maybe you did not notice that Rick changed the starting cell to C5?

Please paste code between code tags to keep the tab structure. Click the # icon on the toolbar to insert the tags.

Sorry Sir...I am completely lost. I do not know how to do any changes in VBA Code except simply replacing lines or so.
Could I ask you please to write the whole VBA Code it how it should be and work correctly.

 

[Rick Rothstein]

Sorry Sir...I am completely lost. I do not know how to do any changes in VBA Code except simply replacing lines or so.
Could I ask you please to write the whole VBA Code it how it should be and work correctly.

The VBA code that you posted in Message #13 works fine exactly as written... nothing needs to be changed. See below for what I think the problem is.

Thank you.....I have done some changes but VBA not working

Sub GetRandomNumbers()
  Dim i As Long, randIndex As Long, Temp As Long, anArray As Variant
  Dim MaxNumber As Long, HowManyRands As Long
  MaxNumber = Range("[B][COLOR="#FF0000"][SIZE=4]A2[/SIZE][/COLOR][/B]").Value
  HowManyRands = Range("[B][COLOR="#FF0000"][SIZE=4]B2[/SIZE][/COLOR][/B]").Value
  anArray = Evaluate("COLUMN(1:" & MaxNumber & ")")
  Randomize
  For i = MaxNumber To 1 Step -1
    randIndex = Int(i * RND + 1)
    Temp = anArray(randIndex)
    anArray(randIndex) = anArray(i)
    anArray(i) = Temp
  Next i
  Range("c5").Resize(, HowManyRands).Value = anArray
End Sub

Did you put your values in cells A2 and B2? If not, put 28 into cell A2 and 6 into cell B2 and then run the code.

 

[Jan Kalop]

I am very sorry for my impatience and the confusion I caused.
Everything by asking for a quick solution to the problem.
Thank you for any help. Now I need more time to consider the proposed solutions, but because of the late hour and fatigue, I will try tomorrow. I am sure that your help was necessary and my distraction and tiredness certainly contributed to many misunderstandings. From what I see at the last moment, my oversight is the main reason that the proposed solutions did not work because of my mistakes.
Thank you and please forgive me.

 

[Jan Kalop]

The VBA code that you posted in Message #13 works fine exactly as written... nothing needs to be changed. See below for what I think the problem is.




Did you put your values in cells A2 and B2? If not, put 28 into cell A2 and 6 into cell B2 and then run the code.

Yes I have made correction and everything is working now 100%
Thank you very much and sorry for all that confusion I caused.