Need help creating a macro splitting up number ranges

Status
Not open for further replies.
R

rostar99

New Member
Joined
Apr 8, 2013
Messages
5
I have to take take a hyphenated number range in a cell and split it into smaller ranges. The macro would examine the range and if it is greater than 2000 create new rows below and fill it with smaller ranges. Other cells will copy over. I have a large number of records to handle so would appreciate any help.
Here is an example of the original file.
Excel 2007
ABCDEFGHIJKL
order numberCopy numberPlan NumberPlan NameTotal PackagesTotal SheetsTotal ImagesControl Number RangeStock Code 1Stock Code 2Stock Code 3Stock Code 4
O'TOOLS AUTO, INC.1000001-1034814UPDATE Z
1034815-1034816

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]105883[/TD]

[TD="align: right"]34814[/TD]
[TD="align: right"]69799[/TD]
[TD="align: right"]139598[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]154730[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



It would like it to look like this.
Excel 2007
ABCDEFGHIJKLMN
Order numberCopy numberBatch NumberPlan NumberPlan NameTotal PackagesTotal SheetsTotal ImagesControl NumberStartControl NumberEndStock Code 1Stock Code 2Stock Code 3Stock Code 4
1-01O'TOOLS AUTO, INC.UPDATE Z
1-02O'TOOLS AUTO, INC.UPDATE Z
1-03O'TOOLS AUTO, INC.UPDATE Z
1-04O'TOOLS AUTO, INC.UPDATE Z
1-05O'TOOLS AUTO, INC.UPDATE Z
1-06O'TOOLS AUTO, INC.UPDATE Z
1-07O'TOOLS AUTO, INC.UPDATE Z
1-08O'TOOLS AUTO, INC.UPDATE Z
1-09O'TOOLS AUTO, INC.UPDATE Z
1-10O'TOOLS AUTO, INC.UPDATE Z
1-11O'TOOLS AUTO, INC.UPDATE Z
1-12O'TOOLS AUTO, INC.UPDATE Z
1-13O'TOOLS AUTO, INC.UPDATE Z
1-14O'TOOLS AUTO, INC.UPDATE Z
1-15O'TOOLS AUTO, INC.UPDATE Z
1-16O'TOOLS AUTO, INC.UPDATE Z
1-17O'TOOLS AUTO, INC.UPDATE Z
1-18O'TOOLS AUTO, INC.UPDATE Z
1-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1000001[/TD]
[TD="align: right"]1002000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1002001[/TD]
[TD="align: right"]1004000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1004001[/TD]
[TD="align: right"]1006000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1006001[/TD]
[TD="align: right"]1008000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1008001[/TD]
[TD="align: right"]1010000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1010001[/TD]
[TD="align: right"]1012000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1012001[/TD]
[TD="align: right"]1014000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1014001[/TD]
[TD="align: right"]1016000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1016001[/TD]
[TD="align: right"]1018000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1018001[/TD]
[TD="align: right"]1020000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1020001[/TD]
[TD="align: right"]1022000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1022001[/TD]
[TD="align: right"]1024000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1024001[/TD]
[TD="align: right"]1026000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1026001[/TD]
[TD="align: right"]1028000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1028001[/TD]
[TD="align: right"]1030000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1030001[/TD]
[TD="align: right"]1032000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]8000[/TD]
[TD="align: right"]1032001[/TD]
[TD="align: right"]1034000[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]105883[/TD]

[TD="align: right"]814[/TD]
[TD="align: right"]1799[/TD]
[TD="align: right"]3598[/TD]
[TD="align: right"]1034001[/TD]
[TD="align: right"]1034814[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"]154730[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1034815[/TD]
[TD="align: right"]1034816[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​



Thank you,
Bob
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

B
  • Question Question
Need help creating a second ListBoxOutput without corrupting the first.
Replies
3
Views
421
Blasirl
B
P
  • Question Question
help creating formula or macro to compile three cells on 8 lines from each tab in workbook
Replies
3
Views
690
pile-it Mark
P
M
  • Solved
In the VBA formula, need to get starting row number from the cell value
Replies
4
Views
1K
Fluff
Fluff
K
  • Question Question
Pivot table help
Replies
1
Views
825
SpillerBD
S
M
  • Question Question
Split numbers in 7 positions and make all possible sets adding 0 in it
Replies
5
Views
1K
motilulla
M

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top