| 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 | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
