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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.