splitting cells from a csv file.

[taz23340]

hi guys,
been out of work for while so havent asked some help, but in a new job that i am helping organize from spreadsheets.

i have two or three ?`s that i was not able to find an answer in the search option.

im converting a file that has one column of part numbers and beside it the price.
all this is pulled from an as400 database.
we have set up a macro to arrange all the columns in the order we need as well as any formating.
what happens is that when pulled from the database some of the part numbers end up all in one cell, which is because they belong to one store so we know why it happens but we right now are manually inserting the corresponding amount of rows to go along with them and manually splitting the numbers and such. i know hard to explain without picture or html but this server wont allow me to do that.
so this is it i have a cell in column a that has lets say anywhere from 1 to ten 4-digit numbers and a few columns over is its price. what i would like to do is create a formula or macro or code to automatically recognize how many numbers in the cell (all seperated by spaces) and create those same number of rows below it so for example if i had 1 till 10 so 0001 0002 0003 0004 etc in the cell they would now be in the same colum. in a new row that did not overwrite a cell with the same issue below it.
i have tried the text to column and then transpose but that is many steps as must create the rows and all that.
i also would like that the total price that is in that first cell a few over so lets say 100$ for the 10 numbers to get divided into the amount of rows evenly, so would end up 10 in each cell below the price.

maybe this will take two seperate codes but if yall could help me this would save us a good few hours a week maybe even a days work a week

thanks
taz

 

[Gates Is Antichrist]

Define a range for the column of parts numbers PLUS ONE EXTRA CELL,
and ensure that in the future it's maintained. Call it "PartNumbers." Then use this - I'd probably put it on a button.

Adapt and test this on COPIES of actual data. It overwrites existing cell contents, and you don't get to "undo" that.

Option Explicit
Const PRICE_OFFSET = 3 'number of columns price is over to the right

'Define a range PartNumbers that covers all of the input column, plus one extra
Sub ProcessNewData()
Dim rngPartNumbers As Range, c As Range
    Set rngPartNumbers = Range("PartNumbers")
    For Each c In rngPartNumbers
        If InStr(1, c, " ", 1) Then
            Call ParseMultipleParts(c)
        End If
    Next c
End Sub
Sub ParseMultipleParts(c As Range)
    'For part "aa bb cc" tokenize using space char, 1 per row(inserting rows);
    'evenly split up the cost PRICE_OFFSET rows to the right
    Dim i As Long, lMultCount As Long, lPos As Long, lPrevPos As Long
    Dim sBuff As String 'string believed to be more efficient than Range here
    Dim dThisPrice As Double
    
    sBuff = Trim(c)
    While InStr(1, sBuff, "  ", 1)  'these are DOUBLE spaces - make them all SINGLE
        sBuff = Replace(sBuff, "  ", " ")
    Wend
    lMultCount = 1
    For i = Len(sBuff) To 1 Step -1
        If Mid(sBuff, i, 1) = " " Then lMultCount = lMultCount + 1
    Next i
    dThisPrice = c.Offset(0, PRICE_OFFSET) / lMultCount
    'lPos and lPrevPos == 0 is presumed
    For i = 0 To lMultCount - 2
        lPos = InStr(lPos + 1, sBuff, " ", 1)
        c.Offset(i, 0).Value = Mid(sBuff, lPrevPos + 1, lPos - lPrevPos - 1)
        c.Offset(i, PRICE_OFFSET) = dThisPrice
        c.Offset(i + 1, 0).EntireRow.Insert
        lPrevPos = lPos
    Next i
    'do the last one
    c.Offset(lMultCount - 1, 0).Value = Mid(sBuff, lPrevPos + 1)
    c.Offset(lMultCount - 1, PRICE_OFFSET) = dThisPrice
End Sub

 

[taz23340]

THANKS ILL TEST THIS OUT TOMOROW MORNING WHEN I GET A COPY OF THE SHEET.

NOW WHEN YOU MEAN TO DEFINE THE RANGE, WHAT HAPPENS IF IT CAN CHANGE EACH MONTH? WOULD I THEN NEED TO JUST RE-DEFINE THE RANGE EACH MONTH THEN RUN THE COMMAND? WHY PLUS ONE CELL?

THANKS

 

[Gates Is Antichrist]

Ouch - try using lower case letters. Yes, redefine the range each month to cover the data plus one cell, then run the macro. "Plus one cell" is a safety procedure so that a final inserted row is still in the range.

In other words, range PartNumbers would not encompass all parts, if the last part was a "multiple," if you didn't have the extra row in the range. The inserted rows below would fall below the range. However by extending the range one extra row, if you insert a row below the last data item, the range expands as needed.

 

[taz23340]

ouch my bad, **** systems i use here have to be in caps. sry never used em before.
reallysry

 

[taz23340]

hi so i created a dummy sheet with cells a1 to a10 with 1 to 10 numbers in it and then created the range called PartNumbers which is a1 to a11, as you said to add one cell.

now it gave me an error that invalid inside code and this following is highlighted yellow

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

i put this in the worksheet1 which is this sheet under selection change.

have i done something wrong?

thanks
oh and my name is kevin,
thanks

 

[taz23340]

stupid taz, ooops, i was supposed to put it in module, ill try it again and see what happens.