dimanche 27 mars 2016

VBA/Macro for multiple condition

I need help to be able to get random data from another worksheet with specific conditions:

If i click a button/run a macro, I should get 4 random samples for all rows that has "AA", 1 random sample for all rows that has "BB", 1 random sample for all rows that has "CC", 3 random samples for all rows that has "DD", and 1 random sample for all rows that has "EE" ...

... FROM rawdata.xlsx "Sheet1" sheet and paste it to tool.xlsm "Random Sample" sheet.

All should happen in one click.

This is my code so far. I can only get specific amount of random data from rawdata.xlsx. I hope someone can help me. thanks in advance

Sub CopyRandomRows()


    Sheets("Random Sample").Select
       Cells.Select
       Range("C14").Activate
       Selection.Delete Shift:=xlUp


 Windows("rawdata.xlsx").Activate
    Rows("1:1").Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Windows("tool.xlsm").Activate
    Sheets("Random Sample").Select
    Rows("1:1").Select
    ActiveSheet.Paste

  Dim source As Range, target As Range, randCount&, data(), value, r&, rr&, c&

  'this defines the source to take the data
  With Workbooks("rawdata.xlsx").Worksheets("Sheet1")
Set source = .Range("A1:" & .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row, .Cells(1, .Columns.Count).End(xlToLeft).Column).Address)
End With

  'this defines the target to paste the data
  Set target = Workbooks("tool.xlsm").Worksheets("Random Sample").Range("A2")

  'this defines the number of rows to generate based on the input in textbox
  randCount = 20
  'this loads the data in an array
  data = source.value

  'this shuffles the rows
  For r = 1 To randCount
    rr = 1 + Math.Round(VBA.rnd * (UBound(data) - 1))
    For c = 1 To UBound(data, 2)
      value = data(r, c)
      data(r, c) = data(rr, c)
      data(rr, c) = value
    Next
  Next

  'this writes the data to the target
  target.Resize(randCount, UBound(data, 2)) = data


End Sub




Aucun commentaire:

Enregistrer un commentaire