Loop doesn't work when I call it in Private Sub Change

Question

I have this long loop written. If I use the code as it is pasted in (1) works without any problem. The loop code basically takes into consideration multiple columns which have similar text and sums up the total value of the data in another column. As I said, (1) works without any problem.

(1)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TestRow As Integer

Dim TestColumn As Integer

Dim Sum As Integer

TestRow = Target.Row

TestColumn = Target.Column

    If (TestColumn = 64) Then


        If (TestRow > 11) Then
            Sum = 0
            EColumnValue = 0
            FColumnValue = 0
            IColumnValue = 0
            APColumnValue = 0
            AQColumnValue = 0

            For i = TestRow To 11 Step -1

                If (i = TestRow) Then

                    Sum = ThisWorkbook.Sheets("New").Cells(TestRow, 64).Value
                    EColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 5).Value
                    FColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 6).Value
                    IColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 9).Value
                    APColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 42).Value
                    AQColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 43).Value
                Else

                    If (EColumnValue = ThisWorkbook.Sheets("New").Cells(i, 5).Value) And (FColumnValue = ThisWorkbook.Sheets("New").Cells(i, 6).Value) And (IColumnValue = ThisWorkbook.Sheets("New").Cells(i, 9).Value) And (APColumnValue = ThisWorkbook.Sheets("New").Cells(i, 42).Value) And (AQColumnValue = ThisWorkbook.Sheets("New").Cells(i, 43).Value) Then
                        Sum = Sum + ThisWorkbook.Sheets("New").Cells(i, 64).Value

                        If (Sum > 100) Then
                            MsgBox "Similar values entered in columns E, F, I, AP and AQ equals more then 100"
                            MsgBox "Please re-enter the correct value in column BL"
                            ThisWorkbook.Sheets("New").Cells(TestRow, 64).Value = ""

                            End If
                    Else

                    End If
                End If
            Next

        End If
    End If

End Sub

Instead if I write the code as in (2) I receive the error "run-time error 424".

Sub loopvba()

Dim TestRow As Integer
Dim TestColumn As Integer
Dim Sum As Integer

TestRow = Target.Row
TestColumn = Target.Column

    If (TestColumn = 64) Then


        If (TestRow > 11) Then
            Sum = 0
            EColumnValue = 0
            FColumnValue = 0
            IColumnValue = 0
            APColumnValue = 0
            AQColumnValue = 0

            For i = TestRow To 11 Step -1

                If (i = TestRow) Then

                    Sum = ThisWorkbook.Sheets("New").Cells(TestRow, 64).Value
                    EColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 5).Value
                    FColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 6).Value
                    IColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 9).Value
                    APColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 42).Value
                    AQColumnValue = ThisWorkbook.Sheets("New").Cells(TestRow, 43).Value
                Else

                    If (EColumnValue = ThisWorkbook.Sheets("New").Cells(i, 5).Value) And (FColumnValue = ThisWorkbook.Sheets("New").Cells(i, 6).Value) And (IColumnValue = ThisWorkbook.Sheets("New").Cells(i, 9).Value) And (APColumnValue = ThisWorkbook.Sheets("New").Cells(i, 42).Value) And (AQColumnValue = ThisWorkbook.Sheets("New").Cells(i, 43).Value) Then
                        Sum = Sum + ThisWorkbook.Sheets("New").Cells(i, 64).Value

                        If (Sum > 100) Then
                            MsgBox "Similar values entered in columns E, F, I, AP and AQ equals more then 100"
                            MsgBox "Please re-enter the correct value in column BL"
                            ThisWorkbook.Sheets("New").Cells(TestRow, 64).Value = ""

                            End If
                    Else

                    End If
                End If
            Next

        End If
    End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Call loopvba

End Sub

The reason why I don't want to use the first code (1) is because in my worksheet I have other VBAs code working based on Private Sub Worksheet_Change(ByVal Target As Range). Henceforth, I google it and I found out that you cannot have more than 1 Private Sub Worksheet_Change(ByVal Target As Range) in your worksheet.

How can i make my loop code to be integrated and work with the other vba codes i wrote under Private Sub Worksheet_Change(ByVal Target As Range). I attached a photo below, so maybe is easier for you to understand my question. Thanks ![enter image description here ]1

Start with putting Option Explicit at the top of the code and then compiling and see if it flags anything — QHarr
Target is out of scope for LoopVBA in version 2. It would need to be passed as an expected parameter to LoopVBA — QHarr
Yes you cannot have more than 1 Worksheet_Change event but you can have multiple conditions in 1 Worksheet_Change :) What exactly are you achieve? — Siddharth Rout
Target practice usually refers to any exercise in which projectiles are fired at a specified target. You do not have to throw projectiles but yes, you have to understand what target means here. Why are you using it like TestRow = Target.Row? What kind of object Target is? What error are you getting? Why are you getting the error that you are getting? What is the meaning of that error? How can you resolve that error? Try debugging the code. This is what I meant by Target Practice which I used humorously :) I can give you the code but I doubt you will learn from it :) — Siddharth Rout

QHarr QHarr · Accepted Answer · 2017-11-29T13:28:10

Target is out of scope for loopvba in version 2. It would need to be passed as an expected parameter.

Sub loopvba(ByRef Target As Range)

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

'code

    loopvba Target

'code

End Sub

Loop doesn't work when I call it in Private Sub Change

1 Answers