+ Reply to Thread
Results 1 to 17 of 17

Loop from variable range

  1. #1
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Loop from variable range

    Hi Guys,

    What's causing the problem here? I have variables for my rows where I get an input from the user with the Columns and request the top and bottom of the rows from another input.

    The problem is the loop doesn't recognise the bottom row so keeps looping. What's causing this? It recognises the values as playing around with the code it does recognise these values.

    Please Login or Register  to view this content.
    End Sub
    If it's been helpful please mark as helpful

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Loop from variable range

    1) Do not select cells on a worksheet to "do stuff" to them with VBA. Only humans have to interact with a sheet physically. VBA can skip the "select" and "activate" stuff 99% of the time.

    2) If you are inserting a known formula in a known column in a known range of cells (TopRow:BottomRow), do not loop, simply insert the formulas all at once:
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 11-27-2016 at 05:15 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    Thanks JB, yes that's much better, I'm just trying to get the basics of looping at the moment.

  4. #4
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    Could anyone advise why this range of this variable column and this variable row is not being recognised?

    I've tried setting them as ranges and just cannot manage to get it to recognise the loop end.

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Hello Mate!
    Because they are not numbers (Integers) rather are strings.
    VBA input function returns a string. If you want a number, you need to use excel's Input function

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Something like this

    Please Login or Register  to view this content.
    I also void using do and until while loops. Lots of people (Including myself) fell in infinite trap when they are on initial learning curve. VBA has two for loops which have start and end points.

  7. #7
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    HI AB33, good thanks mate, hope you're well, thanks for your input bud. I did try setting them as Integers, haven't heard of this illusive 'Input Function'. My searches are only showing the below; which relate to input boxes and I know that's not what you're refferring to.

    Could you give us another clue. I've been hitting tutorials still; haven't given up yet. I'm trying to vary the sources of articles as some people explain certain bits really well but miss certain essential bits. The hardest thing I'm finding at the moment is the syntax; I get the gist but material is lacking on the basic syntax I think. a

    http://www.ozgrid.com/VBA/inputbox.htm
    http://www.excel-easy.com/vba/exampl...-function.html

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Well, it is an excel function. VBA and excel are two different applications ( I know it is a weird concept). Excel is the host for VBA. At a basic level, VBA can not stand on its own-it needs a host. There are some functions which are native both to VBA and other functions to excel (Application). In majority cases, both have their own functions, but some times they are overlaps in names, but not necessarily how the functions work. So the input function is one of those chaps which is found on both applications. Despite they share the same name, they do function different. VBA input function can ONLY return one type of return; that is, String, but with excel (Application function, you have 8 choices). If you google application.inputbox, it will take you to your pet hate site.
    Last edited by AB33; 12-01-2016 at 06:34 PM.

  9. #9
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    Thanks AB33, sorry, we posted at the same time. So the input function is setting them as long; presumably , , 1 is setting them to 1 dp?

    I'm getting an error 'named argument not found' with highlighting around the , , Type:= 1)

    Sorry to be such a pest.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range


  11. #11
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    Ah it was Input Box you meant, many thanks. I'm reading into it now, not sure what's causing my varaiable error but obviously something I've missed in your code.

    Thanks again.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Even a string could work. The issue you have is on the next line.

    Range(BarDiameter + ":" + BarDiameter).EntireColumn.Insert

    I sense you want to inset columns based on input box choice. Right?

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Like

    Please Login or Register  to view this content.
    OR

    Columns(BarDiameter).Insert

  14. #14
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    Thanks AB33, ok I'm now getting the user to enter columns as numbers now; unfortunately the loop still is not recognising the address of the two varaiables to stop the loop. This is currently how I have my code:

    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Ah,
    You are fallen in to typical infinite loop. I wish If you had avoided this as per suggestion. Do while is not the best way to get your head around a loop.
    The reason for infinite loop is because the condition Loop Until ActiveCell = BarDiameter + BottomRow.
    will not be true.

    The code knows the entry point but as the condition for exit is not set correctly, it will go around a circle trying to find the exit door. When it consumes all your CPU, it will crash- it is forced landing. Please use for loop which has an entry and exit point.
    My internet connection is extremely slow and so might not respond until tomorrow.


    Please Login or Register  to view this content.
    Last edited by AB33; 12-05-2016 at 05:17 AM.

  16. #16
    Forum Contributor
    Join Date
    02-09-2009
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    380

    Re: Loop from variable range

    Thanks AB, I know it's not the best way but I'm just doing this to learn about looping; just learned that the 'do until' command will not accept an exit point by variables; a valuable lesson consumed by many hours lol. Don't know why this isn't forewarned on the msn website; as the cell is being populated with its position.

    As you can see I'm slowing down as back to work now. It doesn't seem that looprow is a command so presumably you've set up some sort of function with a loop here. To clarify I do not want to start from the top row (row 1), I want to start from the variable TopRow which will alweays be at different positions. Presumably I will not run into problems on the loop with two variables.

    Quite suprised there are so many obstacles; as I thought the whole point of VBA was to be dynamic.

    Thanks again, greatly appreciate the dialogue.

  17. #17
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Loop from variable range

    Easy. For loop is very flexible.
    Replace just a single line

    1 To BottomRow

    TO

    TopRow To BottomRow


    So instead of starting from a fixed position, you start from your chosen position. You can do the same to the end (Last loop).
    There are 2 types of LOOPS in VBA. They are both refereed as FOR LOOP, one of them is suited for a collection; that is, you use to loop in a collection.

    Nothing is easy in life. I had lots of tears and frustration when I started learning VBA.

    Loops are found in all languages. As a beginner, Do while should be avoided at all cost. Once you are fallen in to infinite trap, there is no way out- only crash landing. Even experienced programmers could easily fall into a trap, but they can easily spot the plane is heading in the wrong direction Luckily, I read a book which strongly recommended to avoid Do loops and goto statement. I am glad, I followed the advice.
    VBA is NOT a dynamic language, unlike Python or JavaScript. It is strongly typed scripting language.
    VBA is easy in a sense that, for the majority of time, you do not need to create your own objects- ready meal is given to you, but in Java, there are libraries you can use, but you are left on your own to come up with a Recipe and cook it yourself.
    With the wonderful world of hindsight, I would have preferred to start with Java, so that my mind would have been trained and oriented in OOPs. It is really hard to switch your state of mind from ready meal to making your own meal.
    Last edited by AB33; 12-06-2016 at 06:32 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Loop variable subscript out of range
    By maurice1991 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2015, 07:31 AM
  2. Help reading For loop with range variable
    By Maradona_10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 02:49 PM
  3. Do Loop with variable range
    By Austex_egger in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-08-2013, 03:08 PM
  4. Do Loop with Variable Range
    By Austex_egger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-24-2013, 03:52 PM
  5. Why did an inner loop variable start overwriting the outer loop range suddenly?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 03:24 PM
  6. Referencing variable Range in a loop
    By Samirkc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2006, 07:55 AM
  7. [SOLVED] How to reference variable range in a loop
    By Samirkc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-13-2006, 11:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1