How do I evaluate each cell in a pasted range during a change event?

[calandrelli]

I have a worksheet in Excel. A row represents a record and the row under it is a multiple of the record above it. (Qty x Cost). In the example below let's simplify it and assume that the cost is $2 for any item.

If I use the Change Event I can capture the change and made the cell under the target by multiplied by 2. I use the Application.EnableEvents to stop the code from looping.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range(Target.Address).Offset(1, 0).Value = Range  (Target.Address).Value * 2
Application.EnableEvents = True
End Sub

This works great when the user changes one cell at a time. However the data in the sheet is a monthly forecast and the users are using Copy and Past to paste Jan through December into the row all at once. Passing A$1:A$10 as the target causes it to fail. Can I capture a multicell paste and multiply each cell in the array of values by 2?

 

[calandrelli]

Never mind, I fixed my own problem.

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
  For Each cell In Target
    Range(cell.Address).Offset(1, 0).Value = Range(cell.Address).Value + 1
  Next cell
  Application.EnableEvents = True
End Sub

I just looped through each cell in the range.

 

[SkipVought]

dim t as range

for each t in target
  'what to do with this cell, t

next

BTW, you have created a variable cell implicitly. Much better to use Option Explicit on EVERY MODULE (turn on in Tools)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}