Worksheet_Change Event for 2D pasted array

[St Jimmy]

Hi all,

I am trying to run a QC process when new data is pasted into a specific portion of a workbook.
I have done this in the past with a 1D array by using the Target(i).Row to iterate through rows. But this will only work for 1D arrays:

Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

On Error GoTo err
Application.EnableEvents = False

For i = 1 To Rows.Count

    If Target(i).Row < 106 And Target(i).Row > 4 Then
        'Iteration QC goes here...

When I try the above snippet for a 2D paste it just iterates through the first column of all the rows ignoring the other columns.

In past projects I built QC subroutines that pull the whole sheet into an array and QCs the array.
But that's a lot of work and I feel like I can use a property of the Target object to accomplish this without reinventing the wheel.

Does anyone have a link to an example of iterating through each cell of a pasted 2D array using the Worksheet_Change event?
Or am I doomed to an array subroutine?

Thank you!

 

[DanteAmor]

Does anyone have a link to an example of iterating through each cell

Check this:

VBA Code Error Unable to get the Match property of the WorksheetFunction class

Hi Everyone, I'm trying to create a macro that will run through a set of column headers and look for a specific header ("CMA - ERP Variance") and calculate a simple formula for all the cells within that column header. However, I'm running into an error with my code as I get the "Run-time error...

www.mrexcel.com

 

[St Jimmy]

Thank you Dante, I totally forgot about the not intersect strategy!
That should do it! Thanks for saving me some time