0

I have a vba code to generate random values and then generate random values to the generated random values. I put these in a table and I want to sort them in ascending order, based on the random values in the first column. However, I have used several codes and tried it but I get an error everytime. Anyone knows how to solve this? My code is as follows:

Sub Button1_Click()

Range("A2:A38").Select
 ActiveSheet.Range("$A$2:$A$38").Value = "=RANDBETWEEN(1,12)"

 Range("B2:B38").Select
 ActiveSheet.Range("$B$2:$B$38").Value = "=RANDBETWEEN(1,INDEX({2,2,8,8,8,8,4,2,8,10,4,8},A2))"

Application.Calculate

Range("A2:B38").Select
 ActiveSheet.Range("$A$2:$B$38").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo



 ActiveWorkbook.Save


End Sub
Borit
  • 1
  • I just noticed that the code isn't working properly yet, I thought it would remove all the duplicate combinations but I still see duplicate combinations when I run the code. Anyone know how to solve this? – Borit Mar 13 '19 at 15:13
  • https://stackoverflow.com/questions/55128759/if-cell-a1-is-certain-value-then-b1-is-random-between-two-values looks like similar code... – Darrell H Mar 13 '19 at 15:16

1 Answers1

0

You want to avoid the use of Activate and Select in your macros

Before doing the remove duplicates, you need to convert the result of the formulas into actual values, since sorting RANDBETWEEN formulas will only result in new random numbers:

Range("A2:A38").Formula = "=RANDBETWEEN(1,12)"

Range("B2:B38").Formula = "=RANDBETWEEN(1,INDEX({2,2,8,8,8,8,4,2,8,10,4,8},A2))"

Range("A2:B38").Value = Range("A2:B38").Value

ActiveSheet.Range("$A$2:$B$38").RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

It's also not clear to me if you want to sort the values (as mentioned in the title of your question, or remove duplicates (which is what your code does)

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
  • Thank you for this. I want to remove duplicates and I want to order the first column in ascending order, they have to represent test samples so duplicates should be left out, and they have to be in order so that the test sampling happens from start to end. I tried your code and it seems to work! No duplicates and everything is in order. – Borit Mar 13 '19 at 15:21