| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split data and copy surrounding data
Hi,
Another challenge: I may have, ideally, something like: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 50 .25 etc. These items may be exactly the same, but are made by different mfg, so have different part number. Challenge: I receive data as follows: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 where the mfg has been entered in one cell, using Alt Enter and where the part number has also been entered in one cell using Alt Enter I need to separate these onto separate rows, but need to copy the qty, price, etc. onto these lines as well. I'm fine with adding some columns, using text to columns to divide the data, but have no idea how to then grab the surround data and repeat it. Is there any way to do this? Thanks -- Thanks! Dee |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split data and copy surrounding data
Hi dee,
I hope this is the sub you need: Sub SepAltEnt() Range("A2").Select Do While Not IsEmpty(ActiveCell) firstrow = ActiveCell.Row secondrow = firstrow + 1 nextrow = secondrow AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), ActiveCell) On Error GoTo 0 If AltEntPlace 0 Then Rows(firstrow).Select Selection.Copy Rows(secondrow).Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), Range("B" & firstrow)) On Error GoTo 0 If AltEntPlace 0 Then Range("B" & firstrow) = Left(Range("B" & firstrow), AltEntPlace - 1) Range("B" & secondrow) = Mid(Range("B" & secondrow), AltEntPlace + 1, 256) End If nextrow = secondrow + 1 End If Range("A" & nextrow).Select Loop End Sub Regards, Stefi €ždee€ť ezt Ă*rta: Hi, Another challenge: I may have, ideally, something like: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 50 .25 etc. These items may be exactly the same, but are made by different mfg, so have different part number. Challenge: I receive data as follows: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 where the mfg has been entered in one cell, using Alt Enter and where the part number has also been entered in one cell using Alt Enter I need to separate these onto separate rows, but need to copy the qty, price, etc. onto these lines as well. I'm fine with adding some columns, using text to columns to divide the data, but have no idea how to then grab the surround data and repeat it. Is there any way to do this? Thanks -- Thanks! Dee |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split data and copy surrounding data
Hi Stefi,
Thanks for the code. I have been extremely busy, so just had time to try it. It gave me an error when I ran it, which I resolved by taking out Option Explicit. Now it gives me a syntax error when it gets to: Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) I'm not sure how to resolve this.... any help would be greatly appreciated. -- Thanks! Dee "Stefi" wrote: Hi dee, I hope this is the sub you need: Sub SepAltEnt() Range("A2").Select Do While Not IsEmpty(ActiveCell) firstrow = ActiveCell.Row secondrow = firstrow + 1 nextrow = secondrow AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), ActiveCell) On Error GoTo 0 If AltEntPlace 0 Then Rows(firstrow).Select Selection.Copy Rows(secondrow).Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), Range("B" & firstrow)) On Error GoTo 0 If AltEntPlace 0 Then Range("B" & firstrow) = Left(Range("B" & firstrow), AltEntPlace - 1) Range("B" & secondrow) = Mid(Range("B" & secondrow), AltEntPlace + 1, 256) End If nextrow = secondrow + 1 End If Range("A" & nextrow).Select Loop End Sub Regards, Stefi €ždee€ť ezt Ă*rta: Hi, Another challenge: I may have, ideally, something like: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 50 .25 etc. These items may be exactly the same, but are made by different mfg, so have different part number. Challenge: I receive data as follows: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 where the mfg has been entered in one cell, using Alt Enter and where the part number has also been entered in one cell using Alt Enter I need to separate these onto separate rows, but need to copy the qty, price, etc. onto these lines as well. I'm fine with adding some columns, using text to columns to divide the data, but have no idea how to then grab the surround data and repeat it. Is there any way to do this? Thanks -- Thanks! Dee |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split data and copy surrounding data
Hi Dee,
Can you detect the value of firstrow, secondrow, AltEntPlace at the breakpoint? Stefi €ždee€ť ezt Ă*rta: Hi Stefi, Thanks for the code. I have been extremely busy, so just had time to try it. It gave me an error when I ran it, which I resolved by taking out Option Explicit. Now it gives me a syntax error when it gets to: Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) I'm not sure how to resolve this.... any help would be greatly appreciated. -- Thanks! Dee "Stefi" wrote: Hi dee, I hope this is the sub you need: Sub SepAltEnt() Range("A2").Select Do While Not IsEmpty(ActiveCell) firstrow = ActiveCell.Row secondrow = firstrow + 1 nextrow = secondrow AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), ActiveCell) On Error GoTo 0 If AltEntPlace 0 Then Rows(firstrow).Select Selection.Copy Rows(secondrow).Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), Range("B" & firstrow)) On Error GoTo 0 If AltEntPlace 0 Then Range("B" & firstrow) = Left(Range("B" & firstrow), AltEntPlace - 1) Range("B" & secondrow) = Mid(Range("B" & secondrow), AltEntPlace + 1, 256) End If nextrow = secondrow + 1 End If Range("A" & nextrow).Select Loop End Sub Regards, Stefi €ždee€ť ezt Ă*rta: Hi, Another challenge: I may have, ideally, something like: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 50 .25 etc. These items may be exactly the same, but are made by different mfg, so have different part number. Challenge: I receive data as follows: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 where the mfg has been entered in one cell, using Alt Enter and where the part number has also been entered in one cell using Alt Enter I need to separate these onto separate rows, but need to copy the qty, price, etc. onto these lines as well. I'm fine with adding some columns, using text to columns to divide the data, but have no idea how to then grab the surround data and repeat it. Is there any way to do this? Thanks -- Thanks! Dee |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split data and copy surrounding data
I'm afraid I'm not well versed enough to understand your question or to know
how to provide you with the info. Sorry about that. -- Thanks! Dee "Stefi" wrote: Hi Dee, Can you detect the value of firstrow, secondrow, AltEntPlace at the breakpoint? Stefi €ždee€ť ezt Ă*rta: Hi Stefi, Thanks for the code. I have been extremely busy, so just had time to try it. It gave me an error when I ran it, which I resolved by taking out Option Explicit. Now it gives me a syntax error when it gets to: Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) I'm not sure how to resolve this.... any help would be greatly appreciated. -- Thanks! Dee "Stefi" wrote: Hi dee, I hope this is the sub you need: Sub SepAltEnt() Range("A2").Select Do While Not IsEmpty(ActiveCell) firstrow = ActiveCell.Row secondrow = firstrow + 1 nextrow = secondrow AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), ActiveCell) On Error GoTo 0 If AltEntPlace 0 Then Rows(firstrow).Select Selection.Copy Rows(secondrow).Select Selection.Insert Shift:=xlDown Application.CutCopyMode = False Range("A" & firstrow) = Left(Range("A" & firstrow), AltEntPlace - 1) Range("A" & secondrow) = Mid(Range("A" & secondrow), AltEntPlace + 1, 256) AltEntPlace = 0 On Error Resume Next AltEntPlace = WorksheetFunction.Search(Chr(10), Range("B" & firstrow)) On Error GoTo 0 If AltEntPlace 0 Then Range("B" & firstrow) = Left(Range("B" & firstrow), AltEntPlace - 1) Range("B" & secondrow) = Mid(Range("B" & secondrow), AltEntPlace + 1, 256) End If nextrow = secondrow + 1 End If Range("A" & nextrow).Select Loop End Sub Regards, Stefi €ždee€ť ezt Ă*rta: Hi, Another challenge: I may have, ideally, something like: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 50 .25 etc. These items may be exactly the same, but are made by different mfg, so have different part number. Challenge: I receive data as follows: Mfg Part Number Qty Price abc 111222 50 .25 xyz 333554 where the mfg has been entered in one cell, using Alt Enter and where the part number has also been entered in one cell using Alt Enter I need to separate these onto separate rows, but need to copy the qty, price, etc. onto these lines as well. I'm fine with adding some columns, using text to columns to divide the data, but have no idea how to then grab the surround data and repeat it. Is there any way to do this? Thanks -- Thanks! Dee |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Split data and copy surrounding data
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to split cell data? | Excel Discussion (Misc queries) | |||
| split data | Excel Discussion (Misc queries) | |||
| how to split data into columns and arrange the resulting data | Excel Discussion (Misc queries) | |||
| Split Data and Concatenate | Excel Programming | |||
| Still Split Data and Concatenate | Excel Programming | |||
Linear Mode
