Macro

[cool]

Hello,
I have a query in macro.
I have 2 excel sheet. In 1st sheet i have list of rows. For eg: 20 rows (may vary)(5 columns).
In 2nd sheet 1-3 names.
Now, If in 2nd sheet, i have one name, then in 1st sheet at the end column (6th column) , i want that names to be displayed.
If in 2nd sheet, there are 2 names, then in 1st sheet, i want 1st row as 1st name, 2nd row as 2nd name, 3rd row as 1st name, 4th row as 2nd name and till last row.
If in 2nd sheet, there are 3 names, then in 1st sheet, i want 1st row as 1st name, 2nd row as 2nd name, 3rd row as 3rd name, 4th row as 1st name and so on.
Is this possible in macro????

 

[venkat1926]

what is the configuration of second sheet . how are the names entered and in which column . give an example of three names

 

[gbrandreth]

Use a double loop.

Set 2 variables to count the number of names on sheet 2 (loop1), and to work out where you last row is on sheet 1 (loop2).

For Y = 1 to Loop2 step Loop1
For X = 1 to Loop1


Use Loop1 to get the value of the name off sheet 2 based on value of Loop1
Then put that value in Col 6
The step bit makes sure you know when you get to the end, you may get an odd entry at end that you have to delete if you dont have an exactly divisible quantity, but its no big deal. you can make it more sophisticated to avoid this, for example checking there is a value on sheet 1, and if blank exit, but I wouldn't bother.

Next X
Next Y

This will go down the sheet and put 1st name all way down if only 1
and will copy in blocks of 2/3 with each of name if theres more than 1


e.g.

Sheet 1 =

A
B
C
D
E
F

Sheet 2 =
Fred
Ralph
Dave


Result would be
A Fred
B Ralph
C Dave
D Fred
E Ralph
F Dave

From you description, I presume this is what your after, and it will work regardless of how many names are on sheet 2, just looping round sequentially.

As the previous poster says, without a better idea of how your sheets are layed out, I can't give you the full code, but its fairly basic stuff if you've written macros before.