VB Script (Excel) help.

[mwvirk]

following script will check cell ‘I’ and if there is something then it will fill date in ‘L’ and time in ‘M’
Then going down in this script; if you enter data in cell ‘T’ then it will insert current date in cell ‘U’
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Problem is that my data is only up-to row 210 (minimum) or 350 (maximum) but if I enter something anywhere in the sheet in cell ‘I’ or ‘T’ it will still fill cell ‘L’ – ‘M’ (if data is entered in cell ‘I’) and fill current date in cell ‘U’ (if something is entered in cell ‘T’)
<o> </o>
Only possibility to stop it is to keep checking cell ‘A’ – if there is something in cell a then cell ‘I’ or/and cell ‘T’ should accept enter otherwise leave the sheet as it is.
<o> </o>
Please help – thanks.
---------------------------------
<o> </o>
Option Explicit
<o> </o>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim irow As Long
If Not Intersect(Target, Range("I:I")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro
<o> </o>
If IsEmpty(Target) Then
Target.Offset(0, 3).ClearContents
Target.Offset(0, 5).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
Target.Offset(0, 3) = Date
Target.Offset(0, 5) = Time
End If
'---------------------------------
If Not Intersect(Target, Range("T:T")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
irow = Target.Row
'if there is a date in column L end the macro
<o> </o>
If IsEmpty(Target) Then
Target.Offset(0, 1).ClearContents
GoTo EndProc
End If
If IsDate(Target.Offset(0, 3)) Then GoTo EndProc
Target.Offset(0, 1) = Date
End If
EndProc:
Application.EnableEvents = True
End Sub

 

[Andrew Poulsom]

Try putting this at the begiining of your procedure:

If Len(Range("A" & Target.Row).Value) = 0 Then Exit Sub