Multi Criteria Macro

[abesimpson]

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

[mdmackillop]

You don't specify where in the row the data is to be pasted.
[VBA]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
[/VBA]

[abesimpson]

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]

[vba]

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
[/vba]

[abesimpson]

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]

Change cel to cell in the offending line.

[abesimpson]

Doh!

Thanks very much to all

a

[abesimpson]

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]

You can do them all at once
[VBA]
Cells(11, "AB").Resize(46).Value = Cells(11, "U").Resize(46).Value
[/VBA]

[abesimpson]

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]

You're vey close though!

[abesimpson]

"Close" counts in horseshoes and not much else. Nice of you to say so though.

a