PDA

View Full Version : VBA Chain texts from multiple rows with multiple column conditions into one cell



Julia
02-02-2021, 10:53 AM
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. :bug:

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

Julia
02-03-2021, 02:59 PM
Does anyone has an idea? Maybe even an easier approach than mine?

SamT
02-03-2021, 04:09 PM
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