|
Problem with Do Loop and If
Can anyone help me please?
I am fairly new to writing code and am using a Do Loop combined with an If to find out if there is an empty string or a formula in a cell and if there is to clear the cell completely until the cell it reaches has nothing in it. However, at first whilst looping through the second condition was ignored (formula) and now I get an error message saying I have a Do without a Loop!!!???? I'm going bananas. The code I have written looks like below. If anyone can help I would be eternally grateful. Many thanks. Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select Else ActiveCell.Offset(0, 1).Select Loop |
Problem with Do Loop and If
Hi
You need an End If: If ' ElseIf ' Else ' End If HTH. Best wishes Harald "aehan" skrev i melding ... Can anyone help me please? I am fairly new to writing code and am using a Do Loop combined with an If to find out if there is an empty string or a formula in a cell and if there is to clear the cell completely until the cell it reaches has nothing in it. However, at first whilst looping through the second condition was ignored (formula) and now I get an error message saying I have a Do without a Loop!!!???? I'm going bananas. The code I have written looks like below. If anyone can help I would be eternally grateful. Many thanks. Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select Else ActiveCell.Offset(0, 1).Select Loop |
Problem with Do Loop and If
Thanks, however I did notice that and amended the code - but it is still
skipping. If I run through the code it deletes the empty strings and then it stops at a cell with a formula in it and doesn't delete it. If I remove the loop and step through the code just asan IF it works fine - I don't understand it... This is what the code looks like now, does anyone have any ideas? Do Until ActiveCell.Value = Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select End If Loop "Harald Staff" wrote: Hi You need an End If: If ' ElseIf ' Else ' End If HTH. Best wishes Harald "aehan" skrev i melding ... Can anyone help me please? I am fairly new to writing code and am using a Do Loop combined with an If to find out if there is an empty string or a formula in a cell and if there is to clear the cell completely until the cell it reaches has nothing in it. However, at first whilst looping through the second condition was ignored (formula) and now I get an error message saying I have a Do without a Loop!!!???? I'm going bananas. The code I have written looks like below. If anyone can help I would be eternally grateful. Many thanks. Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select Else ActiveCell.Offset(0, 1).Select Loop |
Problem with Do Loop and If
The form of the If statement you are using is the 'block form' (see XL
VBA help for more on the block and the single-line form). A block form If statement needs an End If to terminate it. A few other points. A statement that is executed in every branch of an IF statement (the ..Offset().Select in your case) can be moved to just after the If; that saves duplication of code, makes the code more transparent and simplifies maintenance. Sub testIt1() Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ElseIf ActiveCell.Value = " " Then ActiveCell.Clear End If ActiveCell.Offset(0, 1).Select Loop End Sub Also, it is not clear what exactly you want to do. Are you trying to search cells until you get to an empty cell? Or something else? Keep this in mind. The use if <Empty is rather tricky. Empty is defined as the unitialized value of a variable. But it has never been clear how that is supposed to work. In my tests with your code, the until condition is *never* true (whether the XL cell has a constant or a formula in it or has nothing in it). Instead you may want to use IsEmpty() Sub testIt3() Do Until IsEmpty(ActiveCell.Value) If ActiveCell.HasFormula = True Then ActiveCell.Clear ElseIf ActiveCell.Value = " " Then ActiveCell.Clear End If ActiveCell.Offset(0, 1).Select Loop End Sub There is a difference between the zero length string "" and the string with one space character " ". Depends on what you want to do, you should check that particular test. There is almost never a need to select cells/worksheets/whatever. While that is the code that the macro recorder generates, it is far safer -- and, as a bonus, more efficient -- to avoid reliance on active sheets/cells. You may want to look into using a variable for your work. Sub testIt4() Dim aCell As Range Set aCell = ActiveCell Do Until IsEmpty(aCell.Value) If aCell.HasFormula = True Then aCell.Clear ElseIf aCell.Value = " " Then aCell.Clear End If Set aCell = aCell.Offset(0, 1) End With Loop End Sub It is possible to simplify all the references to the same variable with a With statement. At the very least it is easier to maintain the code since one doesn't have to change umpteen references to a variable as I had to do in creating testIt4 above. Sub testIt5() Dim aCell As Range Set aCell = ActiveCell Do Until IsEmpty(aCell.Value) With aCell If .HasFormula = True Then .Clear ElseIf .Value = " " Then .Clear End If Set aCell = .Offset(0, 1) End With Loop End Sub Finally, XL supports a variety of properties and methods that *dramatically* simplify coding (and as a bonus improve performance). To find and clear all cells in the current row to the right of the active cell that have formulas, use Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) _ .SpecialCells(xlCellTypeFormulas).Clear To learn a little more about using XL's object model, see Beyond Excel's recorder http://www.tushar- mehta.com/excel/vba/beyond_the_macro_recorder/index.htm To learn how to read code, see Case Study =3F Understanding code http://www.tushar-mehta.com/excel/vb...and%20code.htm Sorry you asked for help, aren't you? {grin} -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Can anyone help me please? I am fairly new to writing code and am using a Do Loop combined with an If to find out if there is an empty string or a formula in a cell and if there is to clear the cell completely until the cell it reaches has nothing in it. However, at first whilst looping through the second condition was ignored (formula) and now I get an error message saying I have a Do without a Loop!!!???? I'm going bananas. The code I have written looks like below. If anyone can help I would be eternally grateful. Many thanks. Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select Else ActiveCell.Offset(0, 1).Select Loop |
Problem with Do Loop and If
Hi Tushar
Thank you very much, and no, I'm not sorry I asked the question, your reply is very helpful and just what I needed. I did amend the code and got it to work myself, but obviously,not properly. What I did was to amend the Do While which I did as follows: Do Until ActiveCell.Interior.ColorIndex = xlNone If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select End If Loop That worked because I changed the condition for the Do Loop because the spreadsheet has a background colour and I want the code to stop when it reaches the first blank cell. The cells it is search can only have a single space or a formula in them and I need them removed to do a sort, however there is data to the right that I need to keep, so I can't just delete the whole row. The code you gave me is much better so I'll use that and thank you again for all your help. Cheers "Tushar Mehta" wrote: The form of the If statement you are using is the 'block form' (see XL VBA help for more on the block and the single-line form). A block form If statement needs an End If to terminate it. A few other points. A statement that is executed in every branch of an IF statement (the ..Offset().Select in your case) can be moved to just after the If; that saves duplication of code, makes the code more transparent and simplifies maintenance. Sub testIt1() Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ElseIf ActiveCell.Value = " " Then ActiveCell.Clear End If ActiveCell.Offset(0, 1).Select Loop End Sub Also, it is not clear what exactly you want to do. Are you trying to search cells until you get to an empty cell? Or something else? Keep this in mind. The use if <Empty is rather tricky. Empty is defined as the unitialized value of a variable. But it has never been clear how that is supposed to work. In my tests with your code, the until condition is *never* true (whether the XL cell has a constant or a formula in it or has nothing in it). Instead you may want to use IsEmpty() Sub testIt3() Do Until IsEmpty(ActiveCell.Value) If ActiveCell.HasFormula = True Then ActiveCell.Clear ElseIf ActiveCell.Value = " " Then ActiveCell.Clear End If ActiveCell.Offset(0, 1).Select Loop End Sub There is a difference between the zero length string "" and the string with one space character " ". Depends on what you want to do, you should check that particular test. There is almost never a need to select cells/worksheets/whatever. While that is the code that the macro recorder generates, it is far safer -- and, as a bonus, more efficient -- to avoid reliance on active sheets/cells. You may want to look into using a variable for your work. Sub testIt4() Dim aCell As Range Set aCell = ActiveCell Do Until IsEmpty(aCell.Value) If aCell.HasFormula = True Then aCell.Clear ElseIf aCell.Value = " " Then aCell.Clear End If Set aCell = aCell.Offset(0, 1) End With Loop End Sub It is possible to simplify all the references to the same variable with a With statement. At the very least it is easier to maintain the code since one doesn't have to change umpteen references to a variable as I had to do in creating testIt4 above. Sub testIt5() Dim aCell As Range Set aCell = ActiveCell Do Until IsEmpty(aCell.Value) With aCell If .HasFormula = True Then .Clear ElseIf .Value = " " Then .Clear End If Set aCell = .Offset(0, 1) End With Loop End Sub Finally, XL supports a variety of properties and methods that *dramatically* simplify coding (and as a bonus improve performance). To find and clear all cells in the current row to the right of the active cell that have formulas, use Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) _ .SpecialCells(xlCellTypeFormulas).Clear To learn a little more about using XL's object model, see Beyond Excel's recorder http://www.tushar- mehta.com/excel/vba/beyond_the_macro_recorder/index.htm To learn how to read code, see Case Study =3F Understanding code http://www.tushar-mehta.com/excel/vb...and%20code.htm Sorry you asked for help, aren't you? {grin} -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Can anyone help me please? I am fairly new to writing code and am using a Do Loop combined with an If to find out if there is an empty string or a formula in a cell and if there is to clear the cell completely until the cell it reaches has nothing in it. However, at first whilst looping through the second condition was ignored (formula) and now I get an error message saying I have a Do without a Loop!!!???? I'm going bananas. The code I have written looks like below. If anyone can help I would be eternally grateful. Many thanks. Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select Else ActiveCell.Offset(0, 1).Select Loop |
Problem with Do Loop and If
Again, can only say thank you very much. You have really helped me and have
given me really useful information regarding VBA, thank you!!! "Tushar Mehta" wrote: Like I wrote, leverage the XL object model. No loop required. {grin} With Range(ActiveCell, ActiveCell.End(xlToRight)) .SpecialCells(xlCellTypeFormulas).Clear .Replace _ What:=" ", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End With -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar Thank you very much, and no, I'm not sorry I asked the question, your reply is very helpful and just what I needed. I did amend the code and got it to work myself, but obviously,not properly. What I did was to amend the Do While which I did as follows: Do Until ActiveCell.Interior.ColorIndex = xlNone If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select End If Loop That worked because I changed the condition for the Do Loop because the spreadsheet has a background colour and I want the code to stop when it reaches the first blank cell. The cells it is search can only have a single space or a formula in them and I need them removed to do a sort, however there is data to the right that I need to keep, so I can't just delete the whole row. The code you gave me is much better so I'll use that and thank you again for all your help. Cheers "Tushar Mehta" wrote: The form of the If statement you are using is the 'block form' (see XL VBA help for more on the block and the single-line form). A block form If statement needs an End If to terminate it. A few other points. A statement that is executed in every branch of an IF statement (the ..Offset().Select in your case) can be moved to just after the If; that saves duplication of code, makes the code more transparent and simplifies maintenance. Sub testIt1() Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ElseIf ActiveCell.Value = " " Then ActiveCell.Clear End If ActiveCell.Offset(0, 1).Select Loop End Sub Also, it is not clear what exactly you want to do. Are you trying to search cells until you get to an empty cell? Or something else? Keep this in mind. The use if <Empty is rather tricky. Empty is defined as the unitialized value of a variable. But it has never been clear how that is supposed to work. In my tests with your code, the until condition is *never* true (whether the XL cell has a constant or a formula in it or has nothing in it). Instead you may want to use IsEmpty() Sub testIt3() Do Until IsEmpty(ActiveCell.Value) If ActiveCell.HasFormula = True Then ActiveCell.Clear ElseIf ActiveCell.Value = " " Then ActiveCell.Clear End If ActiveCell.Offset(0, 1).Select Loop End Sub There is a difference between the zero length string "" and the string with one space character " ". Depends on what you want to do, you should check that particular test. There is almost never a need to select cells/worksheets/whatever. While that is the code that the macro recorder generates, it is far safer -- and, as a bonus, more efficient -- to avoid reliance on active sheets/cells. You may want to look into using a variable for your work. Sub testIt4() Dim aCell As Range Set aCell = ActiveCell Do Until IsEmpty(aCell.Value) If aCell.HasFormula = True Then aCell.Clear ElseIf aCell.Value = " " Then aCell.Clear End If Set aCell = aCell.Offset(0, 1) End With Loop End Sub It is possible to simplify all the references to the same variable with a With statement. At the very least it is easier to maintain the code since one doesn't have to change umpteen references to a variable as I had to do in creating testIt4 above. Sub testIt5() Dim aCell As Range Set aCell = ActiveCell Do Until IsEmpty(aCell.Value) With aCell If .HasFormula = True Then .Clear ElseIf .Value = " " Then .Clear End If Set aCell = .Offset(0, 1) End With Loop End Sub Finally, XL supports a variety of properties and methods that *dramatically* simplify coding (and as a bonus improve performance). To find and clear all cells in the current row to the right of the active cell that have formulas, use Range(ActiveCell, Cells(ActiveCell.Row, Columns.Count)) _ .SpecialCells(xlCellTypeFormulas).Clear To learn a little more about using XL's object model, see Beyond Excel's recorder http://www.tushar- mehta.com/excel/vba/beyond_the_macro_recorder/index.htm To learn how to read code, see Case Study =3F Understanding code http://www.tushar-mehta.com/excel/vb...and%20code.htm Sorry you asked for help, aren't you? {grin} -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Can anyone help me please? I am fairly new to writing code and am using a Do Loop combined with an If to find out if there is an empty string or a formula in a cell and if there is to clear the cell completely until the cell it reaches has nothing in it. However, at first whilst looping through the second condition was ignored (formula) and now I get an error message saying I have a Do without a Loop!!!???? I'm going bananas. The code I have written looks like below. If anyone can help I would be eternally grateful. Many thanks. Do Until ActiveCell.Value < Empty If ActiveCell.HasFormula = True Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select ElseIf ActiveCell.Value = " " Then ActiveCell.Clear ActiveCell.Offset(0, 1).Select Else ActiveCell.Offset(0, 1).Select Loop |
Problem with Do Loop and If
You are welcome. Glad to be of help.
-- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Again, can only say thank you very much. You have really helped me and have given me really useful information regarding VBA, thank you!!! {snip} |
| All times are GMT +1. The time now is 04:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com