| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for idenitfying text and copying it to a new line
Can anyone help with the following?
I have a spreadsheet of approximately 15000 records where the information has been captured incorrectly. This needs to be Split into its own rows based on an "LO" reference number. The field it looks at can have several types of input. |reference number is 123456LO| |REFERENCE NUMBER IS 123456LO AND 123457LO| |REFERENCE NUMBER IS 123456LO23456LO AND 112233LO| Code:
Sub test_v1()
Dim lRow As Long
Dim i As Long, x
lRow = Range("F" & Rows.Count).End(xlUp).Row
For i = lRow To 2 Step -1
If InStr(1, Cells(i, 6).Value, "LO") 0 Then
x = Split(Cells(i, 6), "LO")
Rows(i + 1 & ":" & i + UBound(x) - 1).Insert
Range(Cells(i, 1), Cells(i + UBound(x) - 1, 5)).FillDown
Cells(i, 6) = Right(x(0), 6) & "LO"
For j = 1 To UBound(x) - 1
Cells(i + j, 6) = Trim(Right(x(j), 6)) & "LO"
Next
End If
Next
End Sub
Line 1 | 123456LO 123457LO - 123458LO | Line 2 | TEXT TEXT TEXT TEXT TEXT TEXT TEXT TEXT 999999LO TEXT| The output always go wrong with the above in cell F1 and F2 Can anyone help with this? Thanks In Advance! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
| Copying text with more than one line | Excel Worksheet Functions | |||
| Copying text from comments to cells with line feed | Excel Programming | |||
| A 2 line text showing up in the Cell in Excel prints in 1 line | Excel Discussion (Misc queries) | |||
| import huge text file line-by-line? | Excel Programming | |||
Threaded Mode
