MrExcel Publishing
Your One Stop for Excel Tips & Solutions

/SelectCopy/Paste multiple cells depending on the data in other range


Posted by pessona on June 16, 2000 9:01 PM

I would like to search in Range(B2:K2) for "*" and then for every column that has the "*", I need to look what is the data in the cells below every "*" found and then those data need to be copied to another cells.
For Example:
I have a range of B2,C2,...L2. I need to search for "*" in this range.
My next Range is B3,C3,...L3. I need to know what is the data contains in each cell in this range depending on the * found above them.
Then those data need to be copied to another cells.
Please advice.
Thanks.


Posted by mads on June 17, 0100 3:25 AM


Dim cell As Range
For Each cell In Range("B2:L2")
If cell = "*" Then cell.Offset(1, 0).Copy cell.Offset(7, 0)
Next

mads

Posted by mads on June 17, 0100 8:23 AM


It is not necessary to have a macro to do the above. You can put the following formula in B10 and copy it thru to L10:-

=IF(B2="*",B3,"")

mads

Posted by pessona on June 19, 0100 4:56 PM

mads,
Thanks a zillion.
pessona.

Posted by pessona on June 18, 0100 6:19 PM

Hi mads,
It works already. But the problem now is that there are blanks cells in between those copied data. How can I automatically delete those blank cells so that they would appear just like the example below?

WITH BLANK CELLS:
Q1 Q5 Q8

WITHOUT BLANK CELLS:
Q1Q5Q8 (<-- I need them to come out like this)

TIA mads.

Posted by mads on June 18, 0100 10:00 PM


Dim cell As Range
For Each cell In Range("B2:L2")
If cell = "*" Then cell.Offset(1, 0).Copy Range("IV10").End(xlToLeft).Offset(0, 1)
Next

mads

Posted by mads on June 16, 0100 10:29 PM


You didn't mention where you want to paste the copied data. The following code will put it row 4 in the same column.

Dim cell As Range
For Each cell In Range("B2:L2")
If cell = "*" Then cell.Offset(1, 0).Copy cell.Offset(3, 0)
Next

mads

Posted by pessona on June 16, 0100 11:05 PM

I'm sorry, the copied data should be pasted in range B10:L10.
Thanks in advance Mads!