• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Do loop

needforvba

New Member
hi,i had been learning vba from the past couple of days.i m facing problem in learning the DO loop.I m trying to print the text "MICROSOFT" in the cells A1 until the cell value is "Excel"

here is the code i had tried.I could not get the desired result.Please help me where i went wrong.also the excel hangs up when running this code.


Sub testmacro()

Range("A1").Select

ActiveCell.FormulaR1C1 = "Microsoft"

Do Until ActiveCell.FormulaR1C1 = "excel"

Loop

End Sub

thanks.
 
Hi ,


The first thing to understand is that a loop , in any programming language , needs to have a way to break out of the loop. Otherwise , you will have what is known as an infinite loop , which continues forever , without end.


The second is that before you program any situation , just work it out for yourself , by hand , and visualize how the computer will carry out each step through the program.


1. You are selecting a particular cell using the Range("A1").Select statement


2. You are specifying that the cell will be put to the text "Microsoft".


3. You are checking to see whether the cell contains the text "excel" ; since you have through the earlier statement , made the cell contain the text "Microsoft" , this check will return a FALSE value.


4. Since you are using a DO UNTIL loop , the computer will repeat step 3 again and again , waiting for the result to be TRUE. Since nothing within the program can change the contents of the cell A1 from "Microsoft" to "excel" , the computer will never exit from the loop. To the user , it will appear as if the computer has hung , but it is merely continuing with its check , executing the DO UNTIL loop again and again.


5. When you code any loop , remember the following :


a) A DO WHILE loop keeps executing the steps within the loop as long as the condition it is checking for is TRUE ; for the computer to exit the loop , some part of the code ( between the DO WHILE statement and the LOOP statement ) has to make the condition FALSE.


b) A DO UNTIL loop keeps executing the steps within the loop as long as the condition it is checking for is FALSE ; for the computer to exit the loop , some part of the code ( between the DO UNTIL statement and the LOOP statement ) has to make the condition TRUE.


Narayan
 
thanks NARAYANK991 for the reply and i loved the way you had explained the concept.i had been struggling with the Loops for quite some time back.I dont know why i m feeling a little bit tough with the Loops when i m trying to fiddle around with them.


For & For each loops seems to be alright with me....Had to work for some more time on these Do loops.I did got the concept of them but was unsuccessful in getting the code right.


Coming to my code,what necessary changes do i need to make in order to execute the program correctly...
 
Hi ,


It all depends on what you want to do.


Let us assume that the cells A5 through A17 are blank , and the cell A18 has the text "excel" in it.


Now , if you want to fill up all the blank cells from A5 through A17 with the text "Microsoft" , and stop when you come to the non-blank cell , A18 , which has "excel" in it , the following code will do this :


Sub testmacro()

Range("A5").Select

Do Until ActiveCell.FormulaR1C1 = "excel"

ActiveCell.FormulaR1C1 = "Microsoft"

Activecell.Offset(1,0).Activate

Loop

End Sub


You need to note the following things about the loop :


1. The DO UNTIL loop will continue executing the statements within the loop , until the specified condition ( ActiveCell.FormulaR1C1 = "excel" ) becomes TRUE.


2. If the condition is TRUE when the statement ( DO UNTIL ) is executed the first time , the statements within the loop will never be executed.


3. If the condition in initially FALSE , then it has to become TRUE sometime during the execution of the loop ; in this case , because you are stepping through the cells , from A5 to A6 to A7 .... , you are sure that you will come to A18 , which means the program will exit from the loop at that stage. The stepping through the cells is done by the statement Activecell.Offset(1,0).Activate


Feel free to ask , in case you want any clarifications.


Narayan
 
Thanks Mr.Narayan.This is exactly what i want.....

I learnt the differences what you said comparing both my code & your code.

Your explanation made me understand the concept very clearly.

Now i feel better & confident writing DO loops & i will remember the IMPORTANT points you had stressed. I will try experimenting more with this DO loops.

Looking for your good support in need from you.

Thanks a lot........
 
Hi ,


Thanks a lot. Keep visiting this forum ; I am sure , with time and effort , you'll become an Excel expert.


Narayan
 
Yeah,I will definetly be visiting the forum from now onwards for becoming proficient in excel vba.

I learnt that by recording the macros it will be easy in writing code.

But how to learn the programming concepts like the above when the same task is to be done repeatedly.
 
Hi ,


Check out a few demo lessons from here :


http://chandoo.org/wp/vba-classes/demo/


Check out this small online book :


http://www.jlathamsite.com/Teach/VBA/ProgrammingInExcelVBA_AnIntroduction.pdf


Narayan
 
Need4VBA

I have just done the Chandoo VBA classes and I knew a bit about VBA prior to this but a lot of my prior learning was learned through painful trial and error. I can definitely recommend the classes as they are totally visual which is different to learning from books. For me seeing people writing code before your eyes and making mistakes and correcting them and taking various approaches I just wasn't aware of was a great step up for me. As you say you can't learn everything from recorded macros. You need to see the programming concepts in action like looping, like arrays and like error trapping. I now find going back to books on VBA make much more sense to me.

Cheers!
 
Also speaking of books NARAYANK991 I meant to thank you for that link. Some bedtime reading! Many thanks

John
 
thanks john, for posting your ideas.I feel the need for more practice & need more time to spend on learning by trial & error method.I too had prior knowledge on vba from the past 5 years.i could track what's written in a code from many examples,but was not perfect in writing macros by myself.at times i felt that i was in the middle of an ocean,trying to swim to the shore.some times successful & sometimes drowned..Haaa haaa haaa.


To conclude,I feel the need for more practice & hands-on experience.........
 
Back
Top