Consulting

Results 1 to 4 of 4

Thread: VBA Chain texts from multiple rows with multiple column conditions into one cell

  1. #1
    VBAX Newbie
    Joined
    Feb 2021
    Location
    Zurich
    Posts
    2
    Location

    VBA Chain texts from multiple rows with multiple column conditions into one cell

    Dear Forum,

    I am new to VBA express, but have already done some VBA coding now and then over the last couple of years. Normally I always find usefull tips to write my codes according to forum suggestions of other user's. Now I have a really complex problem, which I don't get my head around, as it needs many steps, and my head creates an overflow of so many loops.

    My Excel version is 2010.

    Situation:
    - I have a standard report (extracted from SAP, so the headers always look the same), that gives me a text description to multiple "products" in DE, EN and FR languages, but in different rows and not in columns, i.e. one product has a German description with 3 rows, so for example DE1= "Tollstes", DE2= "Produkt", DE3= "der Welt"
    - To illustrate, what I mean, I created a screenshot, see attachments. The source data is in Sheet2 and my output sheet is Sheet1.


    My approach:
    1) First loop (For i=2 to 4) to identify the Material ID, which is in Sheet1 column A --> save this in a variable called "Produktnummer"
    2) Second loop to search for "Produktnummer" in Sheet 2 to identify first line of Material texts in Sheet 2.
    2) Third loop to search for the first language letter within each Produktnummer in sheet2, column G --> i.e. search for "D", then for "E", then for "F" (D, E, F)
    3) 4th loop to search for the number of lines within each language in sheet 2, column I --> i.e. search for 1, 2, 3
    4) If For the Material ID there exist language D, line 1 then store it in Variable "TextD", next if for Material ID there exist language D, line 2 then store it additionally into Variable "TextD" (i.e. similar like TextD=TextD1&" "&TextD2&" "&TexD3), and so on, until the the condition is not met (=no more D textlines in Sheet 1 to this Material)
    5) Then store TextD into the correct cell in Sheet 1, i.e. Worksheets("Sheet1").Cells(i, 2).Value = TextD
    6) Continue with next language...

    As you can see, it gets very complicated. Maybe there is an easier way or doing this. I am open for different approaches.

    Many thanks for your suggestions!

    Kind regards,
    Julia
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screenshot - Final State after Macro.jpg 
Views:	64 
Size:	19.9 KB 
ID:	27841  
    Attached Files Attached Files

  2. #2
    VBAX Newbie
    Joined
    Feb 2021
    Location
    Zurich
    Posts
    2
    Location
    Does anyone has an idea? Maybe even an easier approach than mine?

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,633
    Location
    Outer loop: Break the Material Rows into blocks.
    Middle loop: Blocking the Materials, Set the Material Variable, then either use Find + Previous, or If Not Offset(1,0) = Material to determine the last row of that block.
    Innermost loop: Loop thru the Language symbols of that block, use three
    ifs to concatenate the descriptions into 3 different variables. Write the variable Values.

    No, it's not simple, Yes it does require three loops.

    I think you will need Range variables FirstMat, and LastMat.
    'Before the first loop use
    Set FirstMat = Range("A2")
    '
    '
    'At the end of the outer loop use something like
    If LastMat.Offset(1) = "" Then Exit Sub
    FirstMat = LastMat.Offset(1)
    Next
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Hi, I am new. Thought of different approach other than coding. Select all table and rows -- then Click - insert - Pivot table --> Then Drag "material" to Rows and after this drag "Texte" to Rows area. This gives me data in single coloumn but all tasks are listed under as required.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •