Archive of Mr Excel Message Board
Back to Excel VBA archive index
Back to archive home
/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.
| Check out our Excel VBA Resources
|
 |
 |
 |
 |
 |
Re: /SelectCopy/Paste multiple cells depending on the data in other range
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
Re: /SelectCopy/Paste multiple cells depending on the data in other range
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
Re: /SelectCopy/Paste multiple cells depending on the data in other range
Posted by pessona on June 19, 0100 4:56 PM
mads,
Thanks a zillion.
pessona.
Re: /SelectCopy/Paste multiple cells depending on the data in other range
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.
Re: /SelectCopy/Paste multiple cells depending on the data in other range
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
Re: /SelectCopy/Paste multiple cells depending on the data in other range
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
Re: /SelectCopy/Paste multiple cells depending on the data in other range
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!
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.