Help on Loops and Concatenation

    I have a workbook where I have a list of items in one column on one worksheet with a thickness, width, length and weight of that item. Not all the listed items are available for a given application so there might me a line followed by several blanks followed by 5 lines and so one. This always varies. I want to transfer all the listed items along with their descriptions to another worksheet in the workbook but I want each line filled consecutively on that sheet and I want to concatenate the description and put the weight in a seperate cell.


    In short, I want to loop thru the entire first sheet, extracting data and creating entries on the second sheet.


    I am sure this is just as clear as mud! My descriptive abilities leave something to be desired. So I will try to attach an example.


    Hope someone can help. I'm pretty sure this is very simple but I am still struggling with VBA code.



    :flower:

    Thanks,


    Blue

    Hi mate,


    I've read your question 3 times and can't work out what how your original data is structured. If you can post an example then I'm sure you get some help pretty quickly :)


    Regards,
    Dan

    HTH
    Dan

    I really thought I had added an attachment but it didn't get included probably because I'm not sure how to do that. I selected the file off my hard drive as the attachment but didn't get included. I am trying again.

    Thanks,


    Blue

    Hi mate,


    I just knocked this up really quickly. Take a look and see if it does the trick :)


    Sub ConsolidateData()
    Dim lngRow As Long
    Dim shtDest As Worksheet, shtSource As Worksheet
    Dim strNewVal As String, lngItemNumber As Long


    lngItemNumber = 1


    Set shtSource = Sheets("Sheet1")
    Set shtDest = Sheets("Sheet2")


    For lngRow = 2 To shtSource.UsedRange.Rows.Count
    If Len(shtSource.Cells(lngRow, 1)) > 0 Then 'Bring in this row



    strNewVal = "(" & shtSource.Cells(lngRow, 1) & ") " 'Quantity
    strNewVal = strNewVal & shtSource.Cells(lngRow, 2) & " - " 'Description


    If Len(shtSource.Cells(lngRow, 3)) > 0 Then strNewVal = strNewVal & shtSource.Cells(lngRow, 3) 'Thickness


    If Len(shtSource.Cells(lngRow, 4)) > 0 And shtSource.Cells(lngRow, 3) > 0 Then 'Width
    strNewVal = strNewVal & " x " & shtSource.Cells(lngRow, 4)
    Else
    strNewVal = strNewVal & shtSource.Cells(lngRow, 4)
    End If


    If Len(shtSource.Cells(lngRow, 5)) > 0 And shtSource.Cells(lngRow, 4) > 0 Then 'Length
    strNewVal = strNewVal & " x " & shtSource.Cells(lngRow, 5)
    Else
    strNewVal = strNewVal & shtSource.Cells(lngRow, 5)
    End If


    shtDest.Cells(lngItemNumber + 1, 1).Value = lngItemNumber
    shtDest.Cells(lngItemNumber + 1, 2).Value = strNewVal


    shtDest.Cells(lngItemNumber + 1, 3).Value = shtSource.Cells(lngRow, 6).Value


    lngItemNumber = lngItemNumber + 1


    End If


    Next lngRow


    End Sub

    HTH
    Dan

    Thanks so much, dk! Your help is so greatly appreciated. I can use this as a start. Doesn't quite do what I would like but closer than I was able to get. I didn't mention that only the first column (qty) might be blank. There is text in the second column and this macro includes it when I want it skipped. Also I have some rows in the source sheet that I need to have skipped (interim headings - these are always constant). Also I would like the destination line toDrive Bracket Supports - 1" x 24" x 40" Long where only the numbers are included on the source sheet.


    Thanks so much again...

    Thanks,


    Blue

    I appologize, dk, your code did accomplish the size listing correctly. Saw that after I posted the above. Now I just need it to skip the rows whose quantity equals 0.


    :guitar:

    Thanks,


    Blue

    I am reposting this to move it back up front since I am still struggling with this and would really appreciate some help!
    :flower:


    Please, please!

    Thanks,


    Blue

    Ok, either I have not provided a very good example or I have irritated all of you out there which I REALLY hope is not the case! So I am changing the example provided to look a little more like the real thing. I don't dare publish that or my job will be history.


    I would sure be happy for someone to jump in here. DK's code helped but didn't completely solve the problem and I have blown away a lot of worksheets in error trying to get it to behave the way I want it to.


    H...E...L...P!!!! :coolwink:

    Thanks,


    Blue

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login