| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need help
in the column A is by order numbers. in column C is the parts number. between
one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
Sub combinerows()
RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
Joel;
I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
My best guess is the empty cells in column C have some spaces or other wihte
charactters (tab for example). Lets see if a trim() function will help from Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" to Do While Range("A" & (RowCount + 1)) < "" And _ Trim(Range("C" & (RowCount + 1))) = "" "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
Looking futher, I don't think any data is in column C. Clcik with the mouse
any number in column C and then look at the functions box (fx) box at the top of the worksheet to see that the data is really in column C. "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
Joel, I'm as embarrassed as I've ever been. In setting up the OP's example I
omited the Blank Column B ( So my table was Col A was Order Numbers and Col B was the Part numbers. So sorry for my mistake causing the unnecessary work you put in... Once I Inserted a Column (between my Co A and Col B) of my original layout and ran you code It worked perfectly. Now, to understand it I have printed it out and will devote ever how long it takes to understand every "twist-and-turn" in this miracle code. Thanks alot. "Joel" wrote: Looking futher, I don't think any data is in column C. Clcik with the mouse any number in column C and then look at the functions box (fx) box at the top of the worksheet to see that the data is really in column C. "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
The one line below may be hard to decypher
Range("A" & RowCount).NumberFormat = "@" This line just formats the cell as text. When column A is just a single number it is right justified in the cell, the lines with two numbers in column A (205-206) are treated as text and are left justified in the cell. I though it looked bettter to have everything left justified so I turned numbers into text. The code looks at column C in Row + 1 and deletes Row + 1 when it is blank and captures the number in column A as HighNum. When Column C Row + 1 is not a blank it puts HighNum into column A. "Jim May" wrote: Joel, I'm as embarrassed as I've ever been. In setting up the OP's example I omited the Blank Column B ( So my table was Col A was Order Numbers and Col B was the Part numbers. So sorry for my mistake causing the unnecessary work you put in... Once I Inserted a Column (between my Co A and Col B) of my original layout and ran you code It worked perfectly. Now, to understand it I have printed it out and will devote ever how long it takes to understand every "twist-and-turn" in this miracle code. Thanks alot. "Joel" wrote: Looking futher, I don't think any data is in column C. Clcik with the mouse any number in column C and then look at the functions box (fx) box at the top of the worksheet to see that the data is really in column C. "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
Joel,
Thanks for your help on this Loop. These things DRIVE ME NUTS... I've spent 2 hours on your example and progressing all-too-slowly. Are there any ground rules that you can share with me that would help me "sort-out" the initial Steps in mapping out the strategy of doing Loops within Loops. This example and others like it are difficult to me, as I am dislectic, stupid and dumb... rater hard things to overcome when you have chosen the programming field. LOL, LOL Merry Christmas.. Jim "Joel" wrote: The one line below may be hard to decypher Range("A" & RowCount).NumberFormat = "@" This line just formats the cell as text. When column A is just a single number it is right justified in the cell, the lines with two numbers in column A (205-206) are treated as text and are left justified in the cell. I though it looked bettter to have everything left justified so I turned numbers into text. The code looks at column C in Row + 1 and deletes Row + 1 when it is blank and captures the number in column A as HighNum. When Column C Row + 1 is not a blank it puts HighNum into column A. "Jim May" wrote: Joel, I'm as embarrassed as I've ever been. In setting up the OP's example I omited the Blank Column B ( So my table was Col A was Order Numbers and Col B was the Part numbers. So sorry for my mistake causing the unnecessary work you put in... Once I Inserted a Column (between my Co A and Col B) of my original layout and ran you code It worked perfectly. Now, to understand it I have printed it out and will devote ever how long it takes to understand every "twist-and-turn" in this miracle code. Thanks alot. "Joel" wrote: Looking futher, I don't think any data is in column C. Clcik with the mouse any number in column C and then look at the functions box (fx) box at the top of the worksheet to see that the data is really in column C. "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
there is no good way to learn loops except for doing lots of them. then you
start to see the patterns. In shcool you learn start with sorting algorithms. It is a good starting point . the good old basic bubble sort Const Last = 1000 Dim SortData(Last) for i = 1 to (Last - 1) for j = (i + 1) to Last if SortData(i) SortData(j) then 'swap i and j temp = SortData(i) SortData(i) = SortData(j) SortData(j) = temp end if next j next i When you work with excel spreadsheet it is usually moving down rows and either Moving Data, Adding Rows, Deleting Rows. the mving data is the easiest o the 3 because you are usually just increamenting the row counter. Adding rows you have to increase the row counter twice. Deleting Rows you probably don't have to incrtement the row counter because deleting a row does the equivalent. Let me explain my code Sub combinerows() RowCount = 1 'This row simply stops the macro when there is no more data in column A Do While Range("A" & (RowCount + 1)) < "" This statement get the first number in the range of number (200-204) HighNum = Range("A" & RowCount) this statement looks in Row C and checks if the cell in the next row is empty. If it is empty we are going to get the value in column A which is the new HighNum and delete the row. We also need to check column A to make sure we we get to the last row of data we end the macro Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" these two statements get the highNum and delete the next row when the column C is blank in the next row. Note: When the row is deleted all the other rows move up one row. HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop This statement checks is we havve only one number in the range or more than one number in the range. 204 would be a case where we havve more than one number If Range("A" & RowCount) < HighNum Then This statement create the range (202-204) Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If Now we completed a range of number so we have to move to the next row. RowCount = RowCount + 1 Loop End Sub The easy way to see what the code is doing is to single step through the code using F8 key. You can switch back and forth betenn the VBA window and the spreadsheet window and watch as each line is executed. Just go to VBA window and press F8 over and over again. F9 will set break points and F5 will run the code. Look at the menu in VBA under Debug and Run (use this to reset and start from beginning.). "Jim May" wrote: Joel, Thanks for your help on this Loop. These things DRIVE ME NUTS... I've spent 2 hours on your example and progressing all-too-slowly. Are there any ground rules that you can share with me that would help me "sort-out" the initial Steps in mapping out the strategy of doing Loops within Loops. This example and others like it are difficult to me, as I am dislectic, stupid and dumb... rater hard things to overcome when you have chosen the programming field. LOL, LOL Merry Christmas.. Jim "Joel" wrote: The one line below may be hard to decypher Range("A" & RowCount).NumberFormat = "@" This line just formats the cell as text. When column A is just a single number it is right justified in the cell, the lines with two numbers in column A (205-206) are treated as text and are left justified in the cell. I though it looked bettter to have everything left justified so I turned numbers into text. The code looks at column C in Row + 1 and deletes Row + 1 when it is blank and captures the number in column A as HighNum. When Column C Row + 1 is not a blank it puts HighNum into column A. "Jim May" wrote: Joel, I'm as embarrassed as I've ever been. In setting up the OP's example I omited the Blank Column B ( So my table was Col A was Order Numbers and Col B was the Part numbers. So sorry for my mistake causing the unnecessary work you put in... Once I Inserted a Column (between my Co A and Col B) of my original layout and ran you code It worked perfectly. Now, to understand it I have printed it out and will devote ever how long it takes to understand every "twist-and-turn" in this miracle code. Thanks alot. "Joel" wrote: Looking futher, I don't think any data is in column C. Clcik with the mouse any number in column C and then look at the functions box (fx) box at the top of the worksheet to see that the data is really in column C. "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need help
WoW !!
You are one HELPFUL guy !! Thank you for investing in helping me. I just printed off your message and now expect to pour-over it in the next hour, or so. Thanks again, Jim "Joel" wrote: there is no good way to learn loops except for doing lots of them. then you start to see the patterns. In shcool you learn start with sorting algorithms. It is a good starting point . the good old basic bubble sort Const Last = 1000 Dim SortData(Last) for i = 1 to (Last - 1) for j = (i + 1) to Last if SortData(i) SortData(j) then 'swap i and j temp = SortData(i) SortData(i) = SortData(j) SortData(j) = temp end if next j next i When you work with excel spreadsheet it is usually moving down rows and either Moving Data, Adding Rows, Deleting Rows. the mving data is the easiest o the 3 because you are usually just increamenting the row counter. Adding rows you have to increase the row counter twice. Deleting Rows you probably don't have to incrtement the row counter because deleting a row does the equivalent. Let me explain my code Sub combinerows() RowCount = 1 'This row simply stops the macro when there is no more data in column A Do While Range("A" & (RowCount + 1)) < "" This statement get the first number in the range of number (200-204) HighNum = Range("A" & RowCount) this statement looks in Row C and checks if the cell in the next row is empty. If it is empty we are going to get the value in column A which is the new HighNum and delete the row. We also need to check column A to make sure we we get to the last row of data we end the macro Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" these two statements get the highNum and delete the next row when the column C is blank in the next row. Note: When the row is deleted all the other rows move up one row. HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop This statement checks is we havve only one number in the range or more than one number in the range. 204 would be a case where we havve more than one number If Range("A" & RowCount) < HighNum Then This statement create the range (202-204) Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If Now we completed a range of number so we have to move to the next row. RowCount = RowCount + 1 Loop End Sub The easy way to see what the code is doing is to single step through the code using F8 key. You can switch back and forth betenn the VBA window and the spreadsheet window and watch as each line is executed. Just go to VBA window and press F8 over and over again. F9 will set break points and F5 will run the code. Look at the menu in VBA under Debug and Run (use this to reset and start from beginning.). "Jim May" wrote: Joel, Thanks for your help on this Loop. These things DRIVE ME NUTS... I've spent 2 hours on your example and progressing all-too-slowly. Are there any ground rules that you can share with me that would help me "sort-out" the initial Steps in mapping out the strategy of doing Loops within Loops. This example and others like it are difficult to me, as I am dislectic, stupid and dumb... rater hard things to overcome when you have chosen the programming field. LOL, LOL Merry Christmas.. Jim "Joel" wrote: The one line below may be hard to decypher Range("A" & RowCount).NumberFormat = "@" This line just formats the cell as text. When column A is just a single number it is right justified in the cell, the lines with two numbers in column A (205-206) are treated as text and are left justified in the cell. I though it looked bettter to have everything left justified so I turned numbers into text. The code looks at column C in Row + 1 and deletes Row + 1 when it is blank and captures the number in column A as HighNum. When Column C Row + 1 is not a blank it puts HighNum into column A. "Jim May" wrote: Joel, I'm as embarrassed as I've ever been. In setting up the OP's example I omited the Blank Column B ( So my table was Col A was Order Numbers and Col B was the Part numbers. So sorry for my mistake causing the unnecessary work you put in... Once I Inserted a Column (between my Co A and Col B) of my original layout and ran you code It worked perfectly. Now, to understand it I have printed it out and will devote ever how long it takes to understand every "twist-and-turn" in this miracle code. Thanks alot. "Joel" wrote: Looking futher, I don't think any data is in column C. Clcik with the mouse any number in column C and then look at the functions box (fx) box at the top of the worksheet to see that the data is really in column C. "Jim May" wrote: Joel; I have tried and fully tested your code and it doesn't perform as expected. I want **so bad** to understand looping **an this is a perfect example that i wish to understand. Did you test it (your code)? After I run it (the code) the results a 205-215 1234568 << only one line versus the expected 4 lines Can you assist here? TIA, Jim May "Joel" wrote: Sub combinerows() RowCount = 1 Do While Range("A" & (RowCount + 1)) < "" HighNum = Range("A" & RowCount) Do While Range("A" & (RowCount + 1)) < "" And _ Range("C" & (RowCount + 1)) = "" HighNum = Range("A" & (RowCount + 1)) Rows(RowCount + 1).Delete Loop If Range("A" & RowCount) < HighNum Then Range("A" & RowCount) = Range("A" & RowCount) & _ "-" & HighNum Else 'convert column A to text Range("A" & RowCount).NumberFormat = "@" End If RowCount = RowCount + 1 Loop End Sub "John" wrote: in the column A is by order numbers. in column C is the parts number. between one part number to the next part number there are empty box, the empty row are inconsistent. is there anyway to make a macro to del the empty row between the parts number and put the order number togehter. sample below... sample: this is what I want to be: 205 1234568 |205-206 1234568 206 |207 1780009 207 1780009 |209-211 1190001 209 1190001 |212-215 11390008 210 | 211 | 212 11390008 | 213 | 214 | 215 | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
