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