Split data and copy surrounding data

[Guest]

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

 

[Guest]

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:

 

[Guest]

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.

 

[Guest]

Hi Dee,

Can you detect the value of firstrow, secondrow, AltEntPlace at the
breakpoint?
Stefi


„dee†ezt írta:

 

[Guest]

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.

 

[Guest]

When the macro stops because of an error and the debugger displays the
erroneous line highlighted in yellow, open the Locals window from the View
menu, it contains the current values of local variables.
Or send me the original file to (e-mail address removed), and tell me what XL
version you use!

Stefi


„dee†ezt írta: