Macro: copy a range of cells for a list of material

[Geo45]

Hello,

This website helped me a lot since I started learning vba (so thanks a lot), but I couldn't find a solution for the problem I'm facing.

I want to write a macro that will replicate/copy a range of text (line by line) for each material listed in the column A.

Each material in the column A has to have the same text, which is not in one cell, but line by line.
The aim is to "copy" this text for each material in such a way that the material is specified next to each line of the text.

An example is better to understand:

Material	Text
Material 1	Text A
Material 2	Text B
Material 3	Text C
Material 4
Material 5

There are 5 materials in column A and the text consists in 3 lines and is filled in the column B.



The expected result is in column C and D: the text should be the same for each material, and the material next to each line of the text.

Material	Text
Material 1	Text A
Material 1	Text B
Material 1	Text C
Material 2	Text A
Material 2	Text B
Material 2	Text C
Material 3	Text A
Material 3	Text B
Material 3	Text C
Material 4	Text A
Material 4	Text B
Material 4	Text C
Material 5	Text A
Material 5	Text B
Material 5	Text C

Thanks in advance for your help!

[Leith Ross]

Hello Geo45,

Welcome to the Forum!

This VBA macro will do what you want. Copy This Macro into a new VBA Module in your workbook.

You can run the macro in Excel by pressing the keys ALT+F8 , select ListMaterials, and click Run.

Please Login or Register  to view this content.

[Geo45]

Hi Leith Ross,

It works perfectly!
Thanks a lot

I definitely love this website :D

Have a nice day!


-----------------

Regards,

[Geo45]

Hi Leith Ross,

It works perfectly.
But the problem is that I don't really understand the logic.

Actually, I simplified the example given so that it's easier for you to answer me.
And I thought I could adapt the code to my exact need...

But despite my attempts, I cannot change it to meet my needs.

Here's my real problem:

Material	Tag	Text
Material 1	/*	Text A
Material 2	*	Text B
Material 3	?	Text C
Material 4
Material 5

The common text to all materials consists in 2 columns:
One for the “tag” and one for the text.

And the result should be this one:

Material	Text
Material 1	/*	Text A
Material 1	*	Text B
Material 1	?	Text C
Material 2	/*	Text A
Material 2	*	Text B
Material 2	?	Text C
Material 3	/*	Text A
Material 3	*	Text B
Material 3	?	Text C
Material 4	/*	Text A
Material 4	*	Text B
Material 4	?	Text C
Material 5	/*	Text A
Material 5	*	Text B
Material 5	?	Text C

And as I told you, I cannot adapt your code, since I don’t understand.
I tried and obtained results, but not perfect…;

Could you help me again?

Thanks,

[JOHN H. DAVIS]

Another approach:

Please Login or Register  to view this content.

[Leith Ross]

Hello Geo45,

Here is the updated macro. The logic is straightforward. The macro uses arrays to manipulate the data because it is so much faster than copy and paste. With large arrays of data, you want to use arrays. When a range is assigned to a variable that is a Variant type, the variable is dimensioned as a 2-D array with the same number of rows and columns present in the Range. The Range values are copied into the new array. The first index of the array is at {1,1}.

A new array is dimensioned to hold the output data. This array must be large enough to hold all of the new data rows and columns. The number of needed rows is calculated by multiplying the rows in the "Materials" column by the number of rows in either the "Tag" or "Text" columns, since they are the same length. If they were not then the longer of the two would be used.

The number of columns is three: Materials, Tag, Text. The variable InfoOut, which is a Variant type, is redimensioned to the number of rows needed and 3 columns wide. This array is loaded using two loops. An outer loop, j, is used for the rows and an inner loop, k, is used for the columns. An independent row counter, the variable n, is used as the row index for the new array InfoOut. The array InfoIn holds the Range's values. The first column is "Material", the second column is "Tag" and the third column is "Text".

After the InfoOut has been loaded, it is output to cell "E2". This is 4 columns to the left of column "A2". Cell "E2" is used as the starting point for the output range. The range is set to match the number of rows and columns in the array InfoOut. Assigning a Variant array to a Range's value property will load the Range with those values. This is reverse of what was done in paragraph 1.

[]Updated Macro Code[/b]

Please Login or Register  to view this content.

[Geo45]

Hi,

Yes indeed it's very fast...

Thanks for the explanations. It's not yet crystal clear but I'm sure it will be after a few tests
One more question:

How to "paste" the variable InfoOut in another sheet?
I can do it when a basic copy/paste, but here, I cannot do it...

Rng.Cells(1, 1).Offset(0, 4).Resize(UBound(InfoOut), 3).Value = InfoOut


Thanks,
Kr,

[Leith Ross]

Hello Geo45,

You can easily do by using a fully qualified reference to the range on the intended worksheet.

Please Login or Register  to view this content.

Remember to format the column cells to display the data like you want it. This "pastes" only the values, not any formatting.