Macros or functions

[Miloann]

Is there existing macros or functions that I can perform the following
sampling logic?

I have a file with 500K transactions. Would like to start with a number and
select one transaction every 30 records. The total number of selected
transactions are 2000.

Thanks.

 

[bodhi2.71828]

Assuming records are in each row and the first record is in cell A1,
try something like:

Sub Sample2000Step30()
Dim i As Integer
For i = 1 To 2000
Cells(i * 30, 1).Select
'your code here
Next i
End Sub

 

[Myles]

Milo,

Do you really mean *500k *transactions? If so you'll probably have t
wait till Excel12 which will accomodate that number with another 500
to spare.

But if you still want to force the issue you may want to spread you
data over 8 worksheets and then adjust your looping code thus:
Sub N()

Sub m()

For i = 1 To 500000 Step 30

j = i \ 65336

Sheets(j + 1).Cells(i, 2).select

Next
End Sub

Myle

 

[Tim Williams]

Untested, but should work OK

Tim


***************************************************
Sub Tester()
GetSampleRecords "C:\folder\file.txt", 30, _
ThisWorkbook.Sheets("Records").Range("A2")
End Sub


Sub GetSampleRecords(FilePath As String, RecInterval As Integer, _
StartRange As Range)

Dim sTemp As String
Dim iCount As Long
Dim iRow As Long

iCount = 0
iRow = 1

Open FilePath For Input As #1

Do While Not EOF(1)
Line Input #1, sTemp

If iCount Mod RecInterval = 0 Then
StartRange.Offset(iRow, 0).Value = sTemp
iRow = iRow + 1
End If

iCount = iCount + 1
Loop

Close #1


End Sub
****************************************************

 

[Myles]

Sorry, my code is modified to:

Sub m()

For i = 1 To 500000 Step 30

j = i \ 65336
n = i Mod 65536

Sheets(j + 1).Cells(n, "A").select
<Do whatever>

Next

End Sub

Tried and tested.

Myles