Worksheet On Change For Loop Problem

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.... :afrog:

http://i955.photobucket.com/albums/a...dard/excel.jpg

Code:

---

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

---

Welcome to the forums :wave:

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

Quote:

---

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...

Code:

---

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 :)

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