VBA in Excel

[djchaney3]

Currently I have a list of items in coulnm A in column H I have the quantity used and in coulmn E I have comma seperated values (could be 1 could be 30). I have created a Text-To-Columns action to move the info from column E to individual columns for example, if E2 had 3 comma seperated values the code takes these values splits them and moves them to seperate coulmns J, K and L within the same row. Here is the code just incase it brings value to my question. I also have a code that adds rows below based on the quantity count.

'Separate the comma separated values into individual columns
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
        (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
        33, 1), Array(34, 1)), TrailingMinusNumbers:=True

Now, my problem.


I need to take these values from the columns (J, K and L) and move them to Cells E2, E3, and E4 I have the following code, but I am missing something becuase it is not working right.

'Cut seperated values and paste in proper row
i = 2
j = 10
Set CustTag = Cells(i, j)
    Do While Not IsEmpty(CustTag)
        t = CustTag.Value
        l = CustTag.Column
        If t > 0 Then
            Range(t).Cut
            Range("E" & i).Select
            ActiveSheet.Paste
            Set CustTag = CustTag.Offset(0, l + 1)
        Else
            Set CustTag = CustTag.Offset(0, 1)
        End If
    Loop

It is working on the first instance, then it stops... I hope it is an easy fix... any help?

[twinnyfo]

Insert between lines 15-16:

Set CustTag = Cells(i, j)

You have to Set CustTag to the new values of i and j.

However, as an overall advice, I would recommend splitting up your CSV using the Split() function will return an array. Then, instead of copying those values to other cells and then cutting and pasting back to different cells, just puts those values in the new rows you created (but did not show, because apparently your code works for that function).

But, if you can get this to work as is, no need to change anything.

[twinnyfo]

But, I just noticed that you were doing that in line 12...........

[twinnyfo]

Have you stepped through the cod eto see if it is looping through or does it just stop after the first iteration?

It looks like you are not incrementing i (to move to the next row) anywhere....