Syntax / Loop Errors

Question

I am coding a nested loop to run through every cell (row and then column) of a table I have. Each cell has a distinct "combination" that it must be equal to, and if it matches values that I am referencing on another sheet, it will take a value (specified in Analysis worksheet) from that same row and return it in this table. Here's my code. I am getting an error in the larger If statement. Any help is appreciated. Thanks!

Updated code, all works except the last "And..." within the if statement. Without it, the code runs, but not correctly (for obvious reasons). When I include it back in, excel freezes up and it never finishes running. Please help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, j, k, m, n As Long

Worksheets("Chart Data").Range("C6:DR10000").Value = ""

j = 3

Do Until Worksheets("Chart Data").Cells(4, j).Value = ""

    For i = 4 To Worksheets("Chart Data").Cells(Rows.Count, "A").End(xlUp).Row

        k = i + 3
        m = i + 2
        n = 1

        ThisWorkbook.Sheets("Running Avg Log").Activate
        ' If the current row in running avg log doesnt meet this if statement criteria, it skips & increments i w/o doing anything
        If Worksheets("Running Avg Log").Cells(i, 2).Value = 1 _
            And Worksheets("Running Avg Log").Cells(i, 3).Value = n _
            And Worksheets("Running Avg Log").Cells(i, 4).Value = 1 _
            And Worksheets("Running Avg Log").Cells(i, 1).Value = Worksheets("Chart Data").Cells(k, 2).Value Then

            ' When if statement is entered, this sets the selected dimension # in this accepted row and puts it into corresponding spot in chart data
            Worksheets("Chart Data").Cells(m, j).Value = Worksheets("Running Avg Log").Cells(i, 6 + Worksheets("Analysis").Range("C5").Value).Value

            n = n + 1

        End If
    Next i

    ' j (column number) will increment across after each row in one column is done, testing the entire table
    j = j + 1
Loop

End Sub

sheet where code will input the values

sheet where the values are found. If statement looks for date, and the three values following the date. If they match what i want, it returns one of the dimension numbers in that same row. The dimension number is a drop down menu cell selected in the Analysis worksheet, and can be referenced with the cell I have shown in the line within the If statement.

The error is within the If statement, the entire statement is highlighted. — Justin Anderson
My mistake, I misread. When I run it, it slows down/freezes excel to the point where I have to restart. Because I have to restart, there is no error given. The reason I knew where the error was is because it did not freeze once the first time I tried, but I cannot remember what the error read. Since then, its been all freezing. — Justin Anderson
Run-time Error 1004: Application-defined or object-defined error — Justin Anderson

Shai Rado Shai Rado · Accepted Answer · 2016-06-22T12:53:26

The following updated piece of code works without throwing errors. However, not sure what do you want to code to do ?? Where exactly are your table's values ? Where do you want to place them ?

Try attaching a screenshot of the desired worksheet results your are trying to obtain.

Sub GraphLoop()

Dim i, j, k, m                          As Long

Worksheets("Chart Data").Range("C6:DR10000").Value = ""

j = 3
    Do Until Worksheets("Chart Data").Cells(4, j).Value = ""
        For i = 4 To Worksheets("Chart Data").Cells(Rows.count, "B").End(xlUp).row  ' modify according to your Column                k = i + 3
            m = i + 2

            ThisWorkbook.Sheets("Running Avg Log").Activate
            ' If the current row in running avg log doesnt meet this if statement criteria, it skips & increments i w/o doing anything
            If Worksheets("Running Avg Log").Cells(i, 2).Value = 1 _
                And Worksheets("Running Avg Log").Cells(i, 3).Value = 1 _
                And Worksheets("Running Avg Log").Cells(i, 4).Value = 1 _
                And Worksheets("Running Avg Log").Cells(i, 1).Value = Worksheets("Chart Data").Cells(k, 1).Value Then

                ThisWorkbook.Worksheets("Chart Data").Activate
                ' When if statement is entered, this sets the selected dimension # in this accepted row and puts it into corresponding spot in chart data
                Worksheets("Chart Data").Cells(m, j).Value = Worksheets("Running Avg Log").Cells(i, 6 + Worksheets("Analysis").Range("C5").Value).Value
            End If
        Next i

        ' j (column number) will increment across after each row in one column is done, testing the entire table
        j = j + 1
    Loop

End Sub

Syntax / Loop Errors

1 Answers