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.

• Nathan_Sav: What is the error?
• Justin Anderson: The error is within the If statement, the entire statement is highlighted.
• Nathan_Sav: What is the error though, it will say
• 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

Answer 1

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

• Justin Anderson: I've updated with some pictures. I will try your code you just sent and let you know. Thanks!
• Shai Rado: @Justin Anderson , try the code above. I made a modification to the following line For i = 4 To Worksheets("Chart Data").Cells(Rows.count, "B").End(xlUp).row , you need to adjust Column "B" to whatever column holds the datayou are scanning.
• Justin Anderson: You have fixed my freezing problem, however I am still getting an error now with that If statement. Run-time Error 1004: application-defined or object-defined error
• Shai Rado: @Justin Anderson, I am not getting any errors with this code. Do you have other Excel workbooks open ? Make sure your worksheets names are spelled exactly like in your code "Running Avg Log" and "Chart Data".
• Justin Anderson: Nope, no other workbooks open, and spelling is all correct. Am I missing something minuscule here?