RE: VBA to -0- an amount during a Copy/Paste of a range of cells

Tech-Archive recommends: Fix windows errors by optimizing your registry

You are already using the best approach:


Sub demo()
Dim r As Range, r2 As Range
Set r = Sheets("Sheet1").Range("A1:C4")
Set r2 = Sheets("Sheet2").Range("A1")

r.Copy r2

Sheets("Sheet2").Activate
For i = 1 To 4
If Cells(i, "A").Value = Cells(i, "B").Value Then
Cells(i, "C").Value = 0
End If
Next

End Sub

--
Gary's Student


"Dennis" wrote:

2003

Row A B C
1 11 13 1000
2 12 12 3333
3 13 24 2222
4 14 32 4444

The Range being Copy/Paste is "C1:C4"
Using the table above, how do I make the amount Col "C" = 0 if the
value in Col "A" and Col "B" are equal?

Therefore, the Copy/Paste of "C1:C4" becomes:

Row A B C
1 11 13 1000
2 12 12 0
3 13 24 2222
4 14 32 4444

The challenge, as I see it, is what VBA code can I use to evaluate the
array as a whole and zero the amounts as defined above.

I realize that I can do a "Do While - Loop" on the work*** range
after the Paste but that is slower and possibly not the smartest way to
accomplish the task.

The lines of code that I am using follow:

'Copies range from "Sheet1" to "Sheet2"
MyRngToCopy.Offset(0, 5).Resize(, 1).Copy
Sheets("Sheet2").Range("D" & CopyToRngRows).PasteSpecial
Paste:=xlPasteValues

TIA EagleOne


.

Quantcast