G
Guest
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
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