error when randomizing

June 7, 2011 at 09:49:35
Specs: Windows 7
how come i get an error on
Sheets(1).Rows(MyRows(copyRow)).EntireRow.Copy _
Destination:=Sheets(2).Cells(copyRow, 1)

my rows start at row 5, is that the problem?? but i have cell A4 empty. i start titles at B4... my numbers start on A5... i need to have 4 rows above that for titles and such... and i need to have the same 4 rows for titles and such on the sheet 2... help!!


See More: error when randomizing

Report •

#1
June 7, 2011 at 09:52:43
I used this macro:

Option Explicit
Sub Random20()
Randomize 'Initialize Random number seed
Dim MyRows() As Integer ' Declare dynamic array.
Dim numRows, percRows, nxtRow, nxtRnd, chkRnd, copyRow As Integer
'Determine Number of Rows in Sheet1 Column A
numRows = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Get 20% of that number
percRows = numRows * 0.2
'Allocate elements in Array
ReDim MyRows(percRows)
'Create Random numbers and fill array
For nxtRow = 1 To percRows
getNew:
'Generate Random number
nxtRnd = Int((numRows) * Rnd + 1)
'Loop through array, checking for Duplicates
For chkRnd = 1 To nxtRow
'Get new number if Duplicate is found
If MyRows(chkRnd) = nxtRnd Then GoTo getNew
Next
'Add element if Random number is unique
MyRows(nxtRow) = nxtRnd
Next
'Loop through Array, copying rows to Sheet2
For copyRow = 1 To percRows
Sheets(1).Rows(MyRows(copyRow)).EntireRow.Copy _
Destination:=Sheets(2).Cells(copyRow, 1)
Next
End Sub

how come i get an error on
Sheets(1).Rows(MyRows(copyRow)).EntireRow.Copy _
Destination:=Sheets(2).Cells(copyRow, 1)

my rows start at row 5, is that the problem?? but i have cell A4 empty. i start titles at B4... my numbers start on A5... i need to have 4 rows above that for titles and such... and i need to have the same 4 rows for titles and such on the sheet 2... help!!


Report •
Related Solutions


Ask Question