Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros or functions
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros or functions
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros or functions
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 -- Myle ----------------------------------------------------------------------- Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=52294 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros or functions
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 -- Myle ----------------------------------------------------------------------- Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874 View this thread: http://www.excelforum.com/showthread.php?threadid=52294 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine MACROS and functions? | New Users to Excel | |||
macros with functions | Excel Worksheet Functions | |||
Excel Functions and Macros | Excel Discussion (Misc queries) | |||
Finding MACROs and FUNCTIONs | Excel Worksheet Functions | |||
Macros and IF Functions | Excel Programming |