VB: How to copy range of cells with condition??

[bDreamie]

Hi all,
I'm having problem writing a VB macro for excel.

Currently I have:
x = 5

Do While Cells(x, 5) = "New Trade"

Cells(x, 1).Select
Selection.Copy
Cells(x, 12).Select
ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
x = x + 1


Loop

-----------------------------
I need to copy cells(x,1:4) where x,5 = New trade and paste it to cell (x,12) where x starts from row 5. The next record which meet the condition will then paste to cell (6,12) etc. My data has many records where x,5 is <>&quot;New trade&quot;, then followed by the last few records which meets the condition of &quot;New trade&quot;. Is my condition in the loop correct? and how do I copy a range of moving cells?

Thanks so much for all the help I can get on this Cheers

 

[WilMead]

One thing I do is to tag important ranges with a name. Might be a bad habit, but makes it easier for me to think.

In your example, name the first row in the range you need to copy Data_Source. Next name the cell with the first test value Data_Test. Finally name the range that data_source would copy into as Data_Dest.

This turns the formula into
[tt]
if Range(&quot;Data_Test&quot = &quot;New Trade&quot; then
Range(&quot;Data_Source&quot.Copy
Range(&quot;Data_Dest&quot.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= False, _
Transpose:=False
end if
[/tt]

Offset allows you to enhance this to handle your problem.

For example to test all rows below Data_Test containg some value in Data_test:
[tt]
iPos = 0
While Range(&quot;Data_Test&quot.Offset(iPos, 0) <> &quot;&quot;
if Range(&quot;Data_Test&quot.Offset(iPos, 0) = &quot;New Trade&quot; then
Range(&quot;Data_Source&quot.Offset(iPos, 0).Copy
Range(&quot;Data_Dest&quot.Offset(iPos, 0).PasteSpecial Paste:=xlValues
end if
iPos = iPos + 1
wend
[/tt]


Wil Mead
wmead@optonline.net