Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to -0- an amount during a Copy/Paste of a range of cells
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 worksheet 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to -0- an amount during a Copy/Paste of a range of cells
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 worksheet 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy a range of cells and paste into a new workbook in differentcolumns | Excel Discussion (Misc queries) | |||
Copy & Paste Across Sheet Tabs in a Range of Cells | Excel Discussion (Misc queries) | |||
copy/paste won't refer to the appropriate range of cells | Excel Discussion (Misc queries) | |||
Macro to copy range of cells and paste into 1 sheet | Excel Programming | |||
Copy/paste range without resizeing cells | Excel Programming |