Worksheet On Change For Loop Problem

[Pr3ach3r]

Ok here we go... I am sure that I am doing this wrong but this is what I would like to do.

You will see in the image below an X in the column that says MU (make up time) and then of course the coresponding time would be put a column between F - L on the same row. Now there could be more than one instance of MU time so I would like to place the values in column Z and the row they are in. But the for loop I have seems to run in a infinite loop. Any help would be appreciated....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col(7) As String, newvalue
col(1) = "F"
col(2) = "G"
col(3) = "H"
col(4) = "I"
col(5) = "J"
col(6) = "K"
col(7) = "L"
For i = 3 To 46
For k = 1 To 6
 
  If Range("Q" & i).Value <> "" And Range(col(k) & i).Value >= 1 Then
       'Sheets("Monday").Range("A1").Value = -Range(col(k) & i).Value
        newvalue = Range(col(k) & i).Value
        Sheets("Monday").Range("z" & i).Value = Range(col(k) & i).Value
         'MsgBox newvalue
        Exit For
   End If
        
               Next
        Next
theend:
End Sub

[Siddharth Rout]

Welcome to the forums

Two things

1) You should always arrange your code so that it is easily readable. See below the way I have arranged the code...

2) It is entering into an infinite loop because of

Sheets("Monday").Range("z" & i).Value = Range(col(k) & i).Value

The moment you are entering that value, the worksheet change event fires again to avoid that insert this one line as I have done in the code below...

Private Sub Worksheet_Change(ByVal Target As Range)
        
    On Error GoTo TheEnd
    
    '~~> Add this line
    If Target.Column = 26 Then Exit Sub '<~~ Checking if the changes are made to Column Z
    
    Dim col(7) As String, newvalue
    col(1) = "F"
    col(2) = "G"
    col(3) = "H"
    col(4) = "I"
    col(5) = "J"
    col(6) = "K"
    col(7) = "L"
    
    For i = 3 To 46
        For k = 1 To 6
            If Range("Q" & i).Value <> "" And Range(col(k) & i).Value >= 1 Then
                newvalue = Range(col(k) & i).Value
                Sheets("Monday").Range("z" & i).Value = Range(col(k) & i).Value
                Exit For
            End If
        Next
    Next

TheEnd:
    MsgBox Err.Number & ", " & Err.Description
End Sub

Hope this helps

[Pr3ach3r]

Perfect thanks... I will be sure to clean it up before posting next time...