Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
Sum if statement with a left statement | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |