Justin Anderson Justin Anderson - 5 months ago 15
SQL Question

Syntax / Loop Errors

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.

Answer

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
Comments