Tom
Thank you for your reply. I've changed my code as per your revision so that
it's using the For statement. However where I've specified my range eg
If .Range(i, "k&i:n&i").value = "" then etc
vb doesn't like my syntax. I
thought it was possibly wrong so I've changed it to If .Range(i, "k" &i:"n" &
i).value etc but I'm still getting a Expected List Separator error.
Basically i'd like the range "k3:n3" when i = 3.
Can you help please.
Many thanks
Jacqui
"Tom Ogilvy" wrote:
As written, your loop would last forever, because your look condition is
based on checking an unchanging condition.
Do Until Not IsEmpty(Cells(i, 3).Value)
If .Range(i, "k&i:n&i").Value = "" Then
' beeping and annoying message
End if
Loop
You see you check the value of Cells(i,3) over and over again - i is never
changed inside the loop
Here is a suggested revision:
Sub Disable_Check()
With Worksheets("Part B - Coding Details").Columns(3)
Cells.Find(What:="Disable segment values - values/effective", After:= _
ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False).Activate
iFirstRow = ActiveCell.Row + 2
lngLastRow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = iFirstRow To lngLastRow
If .Range(i, "k&i:n&i").Value = "" Then
Beep
MsgBox "You have not indicated that you have carried out all
the necessary checks"
else
exit for
End If
Next
End With
End Sub
--
Regards,
Tom Ogilvy
"Jacqui" wrote in message
...
Can anyone help with my code. I'm trying to test that if Column C
contains a
value across a number of rows then cells M and N must also contain values.
I
thought a Do Until Not Is Empty condition would be most appropriate as I
do
not wish to test every single row, only the rows which contain a value in
Column C. My code is not working however, I'm getting an Object Defined
Error.
Also it is possible that Column C may never actually contain values so I'm
concerned that I've also written an non-escapable loop. I'm sure you
experts
will get the idea of what I'm trying to test, can you help me fix? Also
is
there a neater way of writing this? I'm not sure whether I've used the
best
methods/arguments. My code is copied below
Sub Disable_Check()
With Worksheets("Part B - Coding Details").Columns(3)
Cells.Find(What:="Disable segment values - values/effective", After:= _
ActiveCell, LookIn:=xlValues, LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iFirstRow = ActiveCell.Row + 2
lngLastRow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = iFirstRow To lngLastRow
Do Until Not IsEmpty(Cells(i, 3).Value)
If .Range(i, "k&i:n&i").Value = "" Then
Beep
MsgBox "You have not indicated that you have carried out all
the necessary checks"
End If
Loop
Next
End With
End Sub