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]

Try...

Range("CK5:CR5").Value = anArray

Hope this helps!

 

[Kenneth Hobson]

'Range("ck5:ck11").Value = Application.Transpose(anArray
  Range("ck5").Resize(, 6).Value = anArray

 

[Domenic]

Also, to resize the horizontal range according to the size of anArray...

Range("ck5").Resize(, ubound(anArray)).Value = anArray

 

[Jan Kalop]

Thank you very much to everyone for help. It works like a charm. One more thing, that I learned and thanks for that.

 

[Rick Rothstein]

Thank you very much to everyone for help. It works like a charm. One more thing, that I learned and thanks for that.

Assuming the 28 numbers and the 6 number output are fixed and won't change, then this macro will also work...

Sub TwentyfiveRandOf25ONLY6()
  Dim i As Long, randIndex As Long, Temp As Long, anArray As Variant
  anArray = [COLUMN(1:28)]
  Randomize
  For i = 28 To 1 Step -1
    randIndex = Int(i * Rnd + 1)
    Temp = anArray(randIndex)
    anArray(randIndex) = anArray(i)
    anArray(i) = Temp
  Next i
  Range("c5").Resize(, 6).Value = anArray
End Sub

 

[Jan Kalop]

Great, but how to change the VBA code to be able to change numbers 28 and 6 from cells on worksheet

 

[Jan Kalop]

Assuming the 28 numbers and the 6 number output are fixed and won't change, then this macro will also work...

Sub TwentyfiveRandOf25ONLY6()
  Dim i As Long, randIndex As Long, Temp As Long, anArray As Variant
  anArray = [COLUMN(1:28)]
  Randomize
  For i = 28 To 1 Step -1
    randIndex = Int(i * Rnd + 1)
    Temp = anArray(randIndex)
    anArray(randIndex) = anArray(i)
    anArray(i) = Temp
  Next i
  Range("c5").Resize(, 6).Value = anArray
End Sub

Great, but how to change the VBA code to be able to change numbers 28 and 6 from cells on worksheet

 

[Rick Rothstein]

Great, but how to change the VBA code to be able to change numbers 28 and 6 from cells on worksheet

It would have helped if you had told us those numbers were not fixed originally rather than give us an example which made it seem like they were. Anyway, give this macro a try (you can set the MaxNumber and HowManyRands by whatever method you want...

[table="width: 500"]
[tr]
	[td]Sub GetRandomNumbers()
  Dim i As Long, randIndex As Long, Temp As Long, anArray As Variant
  Dim MaxNumber As Long, HowManyRands As Long
[B][COLOR="#0000FF"]  MaxNumber = 28
  HowManyRands = 6
[/COLOR][/B]  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[/td]
[/tr]
[/table]

 

[Jan Kalop]

It would have helped if you had told us those numbers were not fixed originally rather than give us an example which made it seem like they were. Anyway, give this macro a try (you can set the MaxNumber and HowManyRands by whatever method you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub GetRandomNumbers()
  Dim i As Long, randIndex As Long, Temp As Long, anArray As Variant
  Dim MaxNumber As Long, HowManyRands As Long
[B][COLOR=#0000FF]  MaxNumber = 28
  HowManyRands = 6
[/COLOR][/B]  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[/TD]
[/TR]
</tbody>[/TABLE]

Sorry Sir, but my english is not very good and because of that probably there is so much confusion.
What I am looking for is to ba able to change those numbers ( MaxNumber and MaxNumber) from designated cell on an active sheet