Separating data in a cell, and copying to another column

Question

I have a column named Description, with a description of expenses and an expense date. The file I am getting it from does not have the correct expense date.

I want to separate the data and copy the date to the expense date column and paste the remaining description to the Description column.

The logic in my mind is copy the data and paste to a hidden column and use this code to separate.

Sub text2column()

selection.TextToColumns Destination:=Range("S1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 3), Array(6, 1)), TrailingMinusNumbers:=True

however I need to paste/overwrite starting on the row that have the resource name (e.g Name1, Name2, Name3, etc..) and leaving the not available resource and not touching the description of the not available resource.

In October, is it `102816homeoffice`? That will foul up a Text-to-Columns based on fixed width. — , Feb 16 '16 at 10:28
Nope, that should be January-28-2016, the data Is 012816home-office — deejay, Feb 16 '16 at 12:57
Sorry for being obtuse. What I'm asking is if the 5 digits preceding Home-Office ever become 6 digits e.g. `122516Home - Office` for 25-Dec-2016. For that matter, in the case of 01-Jan-2016, do they become 4 digits? e.g. `1116Home - Office`. — , Feb 16 '16 at 13:01
Nope. the digits are fixed in 6 digits. the 01-Jan-2016 would be 010116home-office — deejay, Feb 17 '16 at 01:43

score 1 · Accepted Answer · edited May 23 '17 at 11:53

I liked how you were basing your processes on the current Application.Selection property. I've got some boilerplate framework for working on the selected cells in this answer.

Sub splitDescription()
    Dim tmp As String, rng As Range

    With Selection
        For Each rng In .Areas
            With rng
                tmp = Cells(.Cells(1).Row, 16383).Address
                .TextToColumns Destination:=.Parent.Range(tmp), DataType:=xlFixedWidth, _
                               FieldInfo:=Array(Array(0, 3), Array(6, 1))
                .Offset(0, -2) = .Parent.Range(tmp).Resize(.Rows.Count, 1).Value
                .Cells = .Parent.Range(tmp).Resize(.Rows.Count, 1).Offset(0, 1).Value
                .Parent.Range(tmp).Resize(1, 2).EntireColumn.Clear
            End With
        Next rng
    End With
End Sub

I've added cycling through the Range.Areas property. This will allow you to cycle through discontiguous ranges of cells in your current selection (e.g. A2:A10, A15:A20). If you have selected a single block of cells, it has an Areas.Count property of 1 so there is no detriment.

Thank you for this Jeeped. I will try to work out with your code and will give you a feedback regarding this. Thanks again — deejay, Feb 17 '16 at 07:47
apologies. but I can't give feedback yet. I am still solving how will I tell the program to leave the not available resource. I will also post the code once I solved it. thanks :) — deejay, Feb 18 '16 at 02:50

score 1 · Answer 2 · edited Feb 18 '16 at 05:57

Thank you very much Jeeped for that Wonderful code. I worked it out to solve how will I skip updating the row that has "Not available" value and came up with this code.

Sub splitDescription()
    Dim tmp As String, rng As Range

    With selection
        For Each rng In .Areas
            With rng
                tmp = Cells(.Cells(1).Row, 16383).Address
                .TextToColumns Destination:=.Parent.Range(tmp), DataType:=xlFixedWidth, _
                               FieldInfo:=Array(Array(0, 3), Array(6, 1))
                .Offset(0, -2) = .Parent.Range(tmp).Resize(.Rows.Count, 1).Value
                .Cells = .Parent.Range(tmp).Resize(.Rows.Count, 1).Offset(0, 1).Value
                .Parent.Range(tmp).Resize(1, 2).EntireColumn.Clear
            End With
        Next rng
    End With

    Range("I4").Select
End Sub

Sub findresource()

    Range("C6").Select
    Do While ActiveCell.Value = "Not Available"
    selection.Offset(1, 0).Select
    Loop

    selection.Offset(0, 5).Select
    Range(selection, selection.End(xlDown)).Select

    Call splitDescription
End Sub

Separating data in a cell, and copying to another column

2 Answers2