Consulting

Results 1 to 4 of 4

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  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:	317 
Size:	19.9 KB 
ID:	27841  
    Attached Files Attached Files

Posting Permissions

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