+ Reply to Thread
Results 1 to 8 of 8

Need help creating a macro splitting up number ranges

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    Belmont, MA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Need help creating a macro splitting up number ranges

    I need to split up records that have greater than 2000 in the control number range column (hyphenated number range). I have over 75,000 records to manipulate so any help would be appreciated.
    The control number range in "Sheet1" (highlighted in yellow) represents the numbers of the individual packages for each record . The first record has 34814 packages and a control number range of 1000001-1034814. As this is greater than 2000 it must be broken up into ranges of 2000 ( start 1000001 end 1002000, start 1002001 end 1004000, etc). The Total Package, Total Sheets, and Total Images are also split up (highlighted in blue). If control number range and packages is equal to or less than 2000 then that row gets copied over to the other sheet with no additional rows and the the control number range number just gets split into a start and end control number (1034815-1034816 changes to start 1034815 end 1034816). The batch number is simply the first digit of the control number "1" combined with the row number(-01, -02 etc.).

    Thank you,
    Bob
    Attached Files Attached Files
    Last edited by rostar99; 04-11-2013 at 12:28 PM.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need help creating a macro splitting up number ranges

    If both sheets are on the same book, try this one, but Batch Number column is blank

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-11-2012
    Location
    Belmont, MA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Need help creating a macro splitting up number ranges

    Quote Originally Posted by AB33 View Post
    If both sheets are on the same book, try this one, but Batch Number column is blank

    Please Login or Register  to view this content.
    AB33
    Thank you for your quick reply. I tried it but it just copied the same information for the number ranges 19 times. The second record has a control range of less than 2000 and it was copied 19 times. I probably did not explain the problem correctly. I will need to break up the control range in chunks of 2000 (and the last row the remainder) into a control start and a control end if the original data control range is greater than 2000. Any records with control range 2000 or less just need to be copied with the control range broken up to a start and end range. The batch numbers I believe could be calculated based on the updated number of records.

    Thanks again,
    Bob
    Attached Files Attached Files
    Last edited by rostar99; 04-11-2013 at 12:30 PM.

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need help creating a macro splitting up number ranges

    Bob,
    I followed your output data. I am not sure I understood you by "control range 2000 or less just" Could you please explain and even better include you sample(On the same book) your desired result?

  5. #5
    Registered User
    Join Date
    10-11-2012
    Location
    Belmont, MA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Need help creating a macro splitting up number ranges

    Quote Originally Posted by AB33 View Post
    Bob,
    I followed your output data. I am not sure I understood you by "control range 2000 or less just" Could you please explain and even better include you sample(On the same book) your desired result?
    AB33,
    TestTRP.xlsm

    The control number range in "Sheet1" (highlighted in yellow) represents the numbers of the individual packages for each record . The first record has 34814 packages and a control number range of 1000001-1034814. As this is greater than 2000 it must be broken up into ranges of 2000 ( start 1000001 end 1002000, start 1002001 end 1004000, etc). The Total Package, Total Sheets, and Total Images are also split up (highlighted in blue). If control number range and packages is equal to or less than 2000 then that row gets copied over to the other sheet with no additional rows and the the control number range number just gets split into a start and end control number (1034815-1034816 changes to start 1034815 end 1034816). The batch number is simply the first digit of the control number "1" combined with the row number(-01, -02 etc.).

    Thanks again,
    Bob

  6. #6
    Registered User
    Join Date
    10-11-2012
    Location
    Belmont, MA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Need help creating a macro splitting up number ranges

    AB33 or anyone who can help,
    Any ideas on how to break up the number ranges and create the new rows with the smaller number ranges? Unfortunately I am facing a deadline and new at VBA.

    Thank you,
    Bob

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Need help creating a macro splitting up number ranges

    Bob,
    Sorry! I had a go, but did not make any progress and hopefully, others will butt in.

  8. #8
    Registered User
    Join Date
    10-11-2012
    Location
    Belmont, MA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Need help creating a macro splitting up number ranges

    No problem AB33,
    I did not realize it would be so difficult when I took it on. Thank you for your effort.

    Bob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1