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
Re: Worksheet On Change For Loop Problem
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 :)
Re: Worksheet On Change For Loop Problem [SOLVED]
Perfect thanks... I will be sure to clean it up before posting next time... :thumb: