For Loop

[riedyp]

Hello,
I am writing a code that has a For loop so that it will check rows 8 to 40 in column 18 of worksheet"DFM Example ". If a question has a red or yellow status there will be a value of 2 or 3 in column column 18.
If there is a 2 or 3 in column 18 then it will select an empty cell in "Project -Action Deck" . It will add the question number in into that cell and the one next to it.
My issue is that my code is not going through the For Loop.
I get an Error on the line under the "If" that states "Worksheets("Project - Action Deck").Cells(EmptyRow, 3).Select".
But I have gotten it to surpass this error and my loop will not make it past the first or second iteration.

 

[StephenCrump]

The error suggests that EmptyRow hasn't been assigned a value correctly.

If Worksheets("Project - Action Deck") is not the ActiveSheet, you'll also need to activate the sheet before you can select a cell.

But you probably don't need to be using .Select at all. Can you post your code?

 

[riedyp]

Sorry about that. here is the code.

Private Sub ActionDeck()
 'Finds the empty row in column C
Dim X As Integer
For X = 8 To 40 Step 1
EmptyRow = Worksheets("Project - Action Deck").Cells(1000, 3).End(xlUp).Row + 1
If Worksheets("DFM-Example").Cells(X, 18) = 2 Or Worksheets("DFM-Example").Cells(X, 18) = 3 Then
Worksheets("Project - Action Deck").Cells(EmptyRow, 3).Select
    ActiveCell.Value = Worksheets("DFM-Example").Cells(13, 1).Value
Worksheets("Project - Action Deck").Cells(EmptyRow, 4).Select
    ActiveCell.Value = Worksheets("DFM-Example").Name
Worksheets("DFM-Example").Cells(X, 19) = Worksheets("Project - Action Deck").Cells(EmptyRow, 2)

End If
Next X
End Sub

 

[Norie]

Does this work any better?

Private Sub ActionDeck()
Dim X As Long

    For X = 8 To 40
    
        'Finds the empty row in column C
        EmptyRow = Worksheets("Project - Action Deck").Cells(Rows.Count, 3).End(xlUp).Row + 1
        
        If Worksheets("DFM-Example").Cells(X, 18) = 2 Or Worksheets("DFM-Example").Cells(X, 18) = 3 Then
            Worksheets("Project - Action Deck").Cells(EmptyRow, 3).Value = Worksheets("DFM-Example").Cells(13, 1).Value
            Worksheets("Project - Action Deck").Cells(EmptyRow, 4).Value = "DFM-Example"
            Worksheets("DFM-Example").Cells(X, 19) = Worksheets("Project - Action Deck").Cells(EmptyRow, 2)
        End If
    Next X
    
End Sub

P.S. Is it always the value from A13 on 'DFM-Example' you want to put in the next empty row in column C on 'Project - Action Deck'?

 

[riedyp]

Yes that works better. As for your question the answer is no, I would like this to to be checked for every row with a question, so rows 8,13,15,17,29 and 32. Realistically if I could just have it check every row from 8 to 50.

Also,
Worksheets("Project - Action Deck").Cells(EmptyRow, 3).Value = Worksheets("DFM-Example").Cells(13, 1).Value
Worksheets("Project - Action Deck").Cells(EmptyRow, 4).Value = "DFM-Example"
Worksheets("DFM-Example").Cells(X, 19) = Worksheets("Project - Action Deck").Cells(EmptyRow, 2)
For some reason after each one of these lines is run it pulls up a code in a different code(see picture) do you see any reason why that may be?

 

[Norie]

As it is the code will check every row from 8 to 40.

If you actually want to check specific rows within that range I think we'll need more information, for example how can we identify if a row has a question?

As for that code triggering the Calculate event it must be because the code itself triggers calculation of the sheet.

If you don't want that to happen you could set calculation to manual while the code runs and then set it back to automatic afterwards.

However you should only really do that if the code in the Calculate event is actually causing problems.

 

[riedyp]

Okay thank you.
"P.S. Is it always the value from A13 on 'DFM-Example' you want to put in the next empty row in column C on 'Project - Action Deck'? "
To answer the above, I do not always want it to equal A13.
If R8 would be equal to 2 or 3 I would want "1)" to appear in "Project Actiondeck"
If R13 would be equal to 2 or 3 I would want "2)" to appear in "Project Actiondeck"
If R15 would be equal to 2 or 3 I would want "3)" to appear in "Project Actiondeck"

and so on, so im wondering how i should edit my code for that.