PDA

View Full Version : Multi Criteria Macro



abesimpson
03-26-2008, 08:13 AM
Once again I am at a loss as how to start.

My problem is in several parts:

1 - Within range "Q11:W56" find rows where CellValue "U" > 0, then:
A) Copy Value in Cell "U" to the same row Cell "AB"
b) Clear Data in Row where CellValue "U" > 0

2 - Copy Values in Range "Y11:AA11" to row where CellValue "U" > 0

Problem 1:
Some of the rows in the "Q11:W56" range may be blank.

Problem 2:
Data must be cleared (step 1A) must be executed before step 2.

So much information on the forum, so little knowledge (on my part).

As always, many thanks in advance.

a http://www.mrexcel.com/forum/images/statusicon/user_online.gif http://www.mrexcel.com/forum/images/buttons/report.gif (http://www.mrexcel.com/forum/report.php?p=1527168) http://www.mrexcel.com/forum/images/buttons/quote.gif (http://www.mrexcel.com/forum/newreply.php?do=newreply&p=1527168)

mdmackillop
03-26-2008, 11:44 AM
You don't specify where in the row the data is to be pasted.
Sub Test()
Dim cel As Range
For Each cel In Intersect(Range("U:U"), ActiveSheet.UsedRange)
If cel > 0 Then
cel.Offset(, 7) = cel
cel.Offset(, -4).Resize(, 7).ClearContents
Range("Y11:AA11").Copy cel.Offset(, 4) '<=====Change offset to suit
End If
Next
End Sub

abesimpson
03-26-2008, 05:14 PM
1 - Within range "Q11:W56" find rows where CellValue "U" > 0, then:
A) Copy Value in Cell "U" where value > 0 to the same row Cell "AB"
B) Clear range "Q:W" Data in Row where CellValue "U" > 0

2 - Copy Values in Range "Y11:AA11" to row where CellValue "U" > 0

Revised text = red

It is important that the range for the code execution be limited to "Q11:W56"

Many thanks (again) for the help.

Bob Phillips
03-27-2008, 02:24 AM
Sub Test()
Dim cell As Range
For Each cell In Range("Q11:Q56")

If Cells(cell.Row, "U").Value > 0 Then

Cells(cell.Row, "U").Copy Cells(cell.Row, "AB")
cell.Resize(, 7).ClearContents
Range("Y11:AA11").Copy cell.Offset(, 4) '<=====Change offset to suit
End If
Next
End Sub

abesimpson
03-27-2008, 05:37 AM
XLD's mod is almost it. The only remaining problem is that the code

Range("Y11:AA11").Copy cel.Offset(, 4) is not working, I get "Run-Time Error 424: Object Required"

Again, What is required is that the Values in Range "Y11:AA11" are copied to the row where CellValue "U" > 0 (This would mean that if "U13" >0 then "Y11:AA11" is copied to "Y13:AA13")

On another note, XLD, I traveled to Chile on business many times in the past. It is one of my absolute favorite countries.


Many thanks

a

mdmackillop
03-27-2008, 05:44 AM
Change cel to cell in the offending line.

abesimpson
03-27-2008, 06:15 AM
Doh!

Thanks very much to all

a

abesimpson
03-27-2008, 06:49 AM
Cells "U11:U56" are fomula. I need to copy the Values to "AB"

Tried several mods, all of which did not work (or course).

Thanks

a

====================================

Found the answer (I think).

Cells(cell.Row, "AB").Value = Cells(cell.Row, "U")

Trust this is correct implementation

Thanks again to all..

a

mdmackillop
03-27-2008, 11:04 AM
You can do them all at once

Cells(11, "AB").Resize(46).Value = Cells(11, "U").Resize(46).Value

abesimpson
03-27-2008, 11:56 AM
Sigh;

Even when I think I'm getting smart I find it's just not up to scratch. LOL

Thanks for the improvement.

a

mdmackillop
03-27-2008, 12:23 PM
You're vey close though! :clap2:

abesimpson
03-27-2008, 02:29 PM
"Close" counts in horseshoes and not much else. Nice of you to say so though.

a