PDA

View Full Version : copy found data to other cloumn



parscon
05-13-2012, 08:27 AM
I have some data on column B with like this (data in 1 cell on column B) :

123, 1234, 12345, 123456, 1234567, 12345678, 123456789

Now i want find and copy these data (123, 12356, 123456789) to column C and delete from column B and also again find and copy data (1234567, 1234, 12345) from column B to column D and delete from B column.

I need this because i have several files that must separate them to other columns .

really thank you very much .

Bob Phillips
05-13-2012, 09:44 AM
Sub ProcessData()
Dim vecValues As Variant
Dim vectmp As Variant
Dim lastrow As Long
Dim i As Long, j As Long

With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To lastrow

If .Cells(i, "B").Value <> "" Then

vecValues = Split(.Cells(i, "B").Value, ",")
For j = 0 To UBound(vecValues) Step 3

ReDim vectmp(1 To 3)
vectmp(1) = vecValues(j)
If j + 1 <= UBound(vecValues) Then vectmp(2) = vecValues(j + 1)
If j + 2 <= UBound(vecValues) Then vectmp(3) = vecValues(j + 2)
.Cells(i, 2 + Int(j / 3) + 1).Value = Join(vectmp, ",")
Next j
End If
Next i

.Columns("B").ClearContents
End With
End Sub