Help with a Do Until statement
 

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

Help with a Do Until statement
 

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

Help with a Do Until statement
 

Hi,
The problem is the statement:

If .Range(i, "k&i:n&i").Value = "" Then

which is an invalid format.

What is tis statement attempting to do? It appears to testing a range (row)
of cells - is this correct. Are "k" and "n" meant to be columns? The
"k&i:n&i" will be treated a text string whereas I think you want the range to
be "k" & i & ":n" & i which solves to "k3:n3" if i=3. Even with this, the
format & test is wrong.

Please provide more detail on what you are trying to do.

HTH

"Jacqui" wrote:

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

Help with a Do Until statement
 

Thank you for your reply. K and n are columns and yes I did think my code
was wrong here. However I've entered "k" & i & ":n" & i but the editor
doesn't like it. Have I got a " or an & in the wrong place because I would
like it to solve to range "k3:n3" if i = 3.
Where you say the test is wrong what would you recommend? Tom Ogilvy has
posted a reply with revised syntax removing the Do Until loop and replacing
this with a For and exit For statement.
However I'm unable to fully test as I getting the Expected List Separator
error.
Can you help?
Many thanks
Jacqui


Are "k" and "n" meant to be columns? The
"k&i:n&i" will be treated a text string whereas I think you want the range to
be "k" & i & ":n" & i which solves to "k3:n3" if i=3. Even with this, the
format & test is wrong.


"Toppers" wrote:

Hi,
The problem is the statement:

If .Range(i, "k&i:n&i").Value = "" Then

which is an invalid format.

What is tis statement attempting to do? It appears to testing a range (row)
of cells - is this correct. Are "k" and "n" meant to be columns? The
"k&i:n&i" will be treated a text string whereas I think you want the range to
be "k" & i & ":n" & i which solves to "k3:n3" if i=3. Even with this, the
format & test is wrong.

Please provide more detail on what you are trying to do.

HTH

"Jacqui" wrote:

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

Help with a Do Until statement
 

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