• 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 with CELLS.

N

needforvba

New Member
hi,

i would like to know how to use the Range or cells property with a Do loop.

I had tried the below code,but i m getting "Method of Object_Global failed" error...


Sub testdo()

Range("A1").Select

Do Until cells(15, 0)

ActiveCell.Value = "Microsoft Excel"

ActiveCell.Offset(1, 0).Select

Loop

End Sub


Any ideas please on using the CELLS property
 
Needforvba


Would you use a For Each loop

[pre]
Code: 
Sub testdo()
Dim c As Range
For Each c In Range("a1:a15")
c.Value = "Microsoft Excel"
Next
End Sub
[/pre]
 
Hi


Try Cells(15,1). You will not get an error. But is this what you want to do ? Use F8 to step through the code.


Narayan
 
narayan the code works fine with the little change.i m giving a try with DO Until loop..

It worked but,i got an error also "TYPE MISMATCH".What could be the reason for the error.
 
Is it mandatory to use the For loops when defining a prefixed range or can we use the DO Until or DO while loops for the situations like the above..
 
If you really want to persist with Do loops

You need to define what the exit condition is

Try:

[pre]
Code: 
Sub testdo()
Range("A1").Select
Do Until ActiveCell.Row = 15
ActiveCell.Value = "Microsoft Excel"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
[/pre]
 
Hi ,


The FOR ... NEXT loop is used where you know the number of times you wish to execute the same code ; if you wish to find out the factorial of any number x , the simplest way is to code it using a FOR ... NEXT loop thus :


Fact = 1

For i = 1 to x

Fact = Fact * i

Next


The DO UNTIL and the DO WHILE loops are used where you do not know in advance how many times the code needs to be executed ; the code is executed repetitively , till the loop condition becomes FALSE or TRUE. This should happen in some execution of the loop , for the program to exit from the loop.


The Type Mismatch error is appearing because the DO UNTIL loop is testing for CELLS(15,1) becoming TRUE ; CELLS(15,1) refers to the cell A15 ; as long as the cell A15 is blank , it is the same as FALSE , and so the test is valid. The moment you put the cell A15 to "Microsoft Excel" , which is a string value , the test DO UNTIL CELLS(15,1) is invalid , since you are checking whether a cell has a TRUE or FALSE value , when in fact it has a string value.


If you change your initial Range selection from A1 to any other column address , say D1 , then you will never have a Type Mismatch error ; but then you will end up filling all the cells till D65536 with the string "Microsoft Excel" !


Alternatively , you can change your test from CELLS(15,1) to CELLS(15,4) , in which case you will be testing the cell D15 for TRUE or FALSE ; again , there will not be any Type Mismatch error.


Narayan
 
Thanks Hui for the updates.

I m keen on it as i m a novice in programming & is interested in learning in achieving the same results with different loops..
 
Hi,

Thanks Narayan for the clear explanation on the DO UNTIL & DO WHILE loops.

From the second para(DO UNTIL & DO WHILE),i understood that in any situation where the looping situation is known in advance,we need to use the FOR LOOPS just as my requirement above.


Is it any kind of error in using the DO UNTIL or DO WHILE loops as per programming rules or logic if i use the DO loops in such kind of situations.
 
Hi ,


Not at all. I think there are three requirements in any program :


1. It should do the job correctly , catering to all kinds of data input / user input , without crashing / hanging or doing anything else unpredictable or unexpected or disastrous.


2. It should allow for future upgradation as easily and conveniently as possible.


3. It should do it in the best possible way i.e. its use of all resources ( computer memory , computer time ,... ) should be optimal.


At least when you starting out as a programmer , I think satisfying the first requirement is great.


As you continue programming , you will come across situations where the difference between a reasonable solution and an optimal solution can be dramatic , which is when you will also start looking for optimal solutions.


It is certainly not an error to do something with a DO .... loop , which can be better done with a FOR .... loop.


Keep programming ; but also , always see if there is a better way ( as Anup has said in this forum in another post ).


Narayan
 
mr.narayan thanks a lot for the encouragement & confidence you had given with your words & explanation.

I am struggling with this Excel vba programming for many days.


Forunately,i had downloaded & read many Pdfs,books,articles & examples from the internet.


on the flip side,these many had made me a little bit confusing also.

I m searching daily on the net for learning vba.some of them are creating confusing whether to use a particular Method or property for the same kind of situations.


The same kind of problems can be solved in different ways using different logics,but as you said in the end result should be Correct & in the efficient way.


i hope & am eagerly hoping for the best support from you and this forum...
 
For the sake of easy understanding of the concepts explained in many articles & examples i m taking general situations as my problems & solving them through various ways.

I feel proceeding in the above way would make me feel confident by starting slowly & picking up the pace with huge projects will eventually lead me to build up my programming skills.I would welcome any kind of small Suggestion or help from you of any sort.


Thanks in advance...
 
Hi ,


Keep at it , and keep trying to solve problems using VBA. You can try out a lot of statements and functions in the Immediate window of your Excel VB project ; within your program the statement Debug.Print can be used to display any intermediate result that you want.


All the best , and keep visiting this forum , and several others of a similar nature. Peltier has a lot of material on his website http://peltiertech.com/


Narayan
 
Needforvba


Although you can do what you want with a number of loop/control structures, these aren't best suited to what your trying to do.


A lot of the loop structures are carry overs from old versions of basic dating well before excel and Object Orientated programming.


When dealing with objects such as cells, shapes, or any other objects that can be grouped together in a collection, the use of the
Code: 
for each
is recommended.

Not only does the structure loop through each of the objects in that collection, it exposes you simply to the underlying properties and methods available to those objects, without the need to keep count or track of which object your upto.


When simply keeping track of numerical loops or loops where you are waiting for user interaction to happen, which don't involve objects the Do/Until and loop/while etc loops are great
 
what strategy or plan do i need to follow in order to become good/proficient in loops.I mean any way to get hands on experience.?
 
Hi ,


Becoming good in programming is like becoming good in a foreign language ; immerse yourself in the language , and use it as much as you can , in various situations.


Finally , programs are used to solve problems , so an interest in solving problems is useful.


Going through forums like these , where problems are highlighted , and solutions proposed , will help you think like a programmer. And of course , read as much as you can , starting with even the Excel help , which has a lot of example code in it.


Narayan
 
Hey needforvba,

The way to get hands-on experience is to come up with problems to solve and then to solve them. If you take a course or buy a book, it will include lessons as well as suggest problems for you to tackle.


With an interest in problem-solving, as Narayan says, you come to see all the things you can achieve in an area of interest, and you will start to have more and more ideas for personal and work projects. I'll add the adjectives "creative", "detailed", "passionate" (good for any pursuit), and "curious" as motivating factors in becoming a skilled programmer. Obvious from this post is your passion and curiosity.


Computers were invented as tools to make our lives easier and to raise our day-to-day achievements in various pursuits to a level that ordinary human resources would not be able to achieve. Computers do nothing until programmed, so it is the programmer's task to find ways to make lives easier, and to increase productivity, achieving things that would otherwise be impossible or impractical, including the more modest sounding goal of just achieving a nicer result than one could achieve without the programmer's labors. A good programmer has many skills, including efficiency expert, aesthetic sensibility, inventive ability to think outside the box, empathetic ability to put themselves in the mind and shoes of the end user so the program is intuitive and best serves the user's needs (the biggest complaints from users have to do with the difficulty in using program interfaces and with them not best suiting their needs), and finally, they have to know or to learn a lot about every subject or business that their programs will be designed to serve.


Lesson number one -- anything is possible (although the solutions to various problems vary in the practicality of their solutions).


~~~


I think you had a great idea to ask for a lesson in various ways to do a particular task, That is a great exercise. Even though sometimes one method or another is best suited for a specific task, this will help you to understand how the various methods relate to each other and hone these tools in your programmer's toolbox. You'll have some experience to help you decide which one to use next time, as well as with how to use it.


~~~


Besides books and classes, which are great, because you have a mentor/guide/teacher to help you learn the concepts and techniques in an efficient way, there is also the method of joining multiple forums, where you can ask for mini lessons and help with specific problems, and (this is also a great teacher) help answer other people's questions and problems; the method of finding online resources and trying to find the solutions to your problems yourself; the method of learning to use the built-in help system to your advantage; the method of learning the basics of how to use the debugging tools in VBA to track down the reasons your code is not working the way you'd like.


A Google search for "VBA lessons" brings up a plethora of websites with free instruction materials. I have no experience with them to make any recommendations, but that's surely a good place to get your feet wet.


~~~~~


A general tip I highly recommend is putting the statement "
Code: 
Option Explicit
" at the top of every code module (before all the procedures). It requires you to declare all variables before using them, which is the "right" way to program :)... VBA is unusual among programming languages in that by default it will create variables on-the-fly as soon as it runs into a statement in your code that appears to rely on a variable name that doesn't yet exist. The biggest problem with that style of programming is that it is very difficult to debug, because VBA will not warn you if you have a typo in a variable name. It's good practice for other reasons too.


Good luck! Keep asking questions and I'm sure you'll enjoy learning to program and having the satisfaction of making your computer do things computers have never done before--at least in your particular way.


Asa
 
You must log in or register to reply here.
Back
Top