Loop Until Cell is Empty

    Hello,


    I wish to add a loop macro to a spreadsheet I'm working on, but have next to no experience. This is what i'd like to do:


    Cell L11 is determined by another cell + cell K11. As cell L11 is directly related to cell K12, i need to iterate it a certain amount of steps or the spreadsheet breaks...


    I have set up another cell (K12) so that it is empty if K11 is within +- 10 from L11, so I want a code that will stop the iteration process when that cell is empty.


    An alternative way of doing it might be to say I only want it to iterate 5 times, and then use that number - I only really need a vague accuracy. (number will vary from 10-1000)


    Can anyone help me? Thanks!

    Re: Loop Until Cell is Empty


    Maybe something like this

    Code
    if Range("K11").value <= Range("L11").value + 10 and Range("K11").value >= Range("L11").value - 10 then
'Do Something
End If

    Re: Loop Until Cell is Empty


    What would I add that code to? Can you help me with the entire code to paste into VBA? And how do I make sure that macro runs all the time? Sorry....

    Re: Loop Until Cell is Empty


    Hit ALT+F11, look to the left to see a list of sheets.
    Double click the one with your values in it and paste this

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("K11").Value <= Range("L11").Value + 10 And Range("K11").Value >= Range("L11").Value - 10 Then
        'Do Something
    End If
End Sub


    Then close that window.
    You should be good to go from there

    Re: Loop Until Cell is Empty


    This code will help you stop your iteration. So it depends on what type of iteration you have.
    If you have a for loop it would be Exit For

    Re: Loop Until Cell is Empty


    Ok. So yes, it would be a Loop Iteration. I would use the Exit For at the end, it comes up the with the error "Exit For not within the For...Next". What Do i need to put before the code to start the iteration?


    I'm sorry i'm such a n00b. :)

    Re: Loop Until Cell is Empty


    Oh I thought you already had the code.
    Well, I don't understand you post #1 can you explain a little better for me to help you?

    Re: Loop Until Cell is Empty


    Of course. I have a value "heat demand", which directly affects another value "heat loss". As the "heat demand" increases, the "heat loss" increases and this is added to the initial heat demand to increase the "heat demand" further. Essentially a loop function (I think).


    At the moment, I manually copy the heat loss to another cell which is added to the heat demand, I would like the worksheet to do this automatically in as simple a way as possible. A iteration with only a few steps.


    I understood from looking briefly that you can iterate until a certain cell is empty, which is the reason I created a cell which uses an IF function (=IF(L11-L12>-10,IF(L11-L12<10,"",1),1) to create this empty cell when the value is close enough. I also understand how your code would help though, using only a maximum of 50 steps to reach values up to 500.


    I just wondered if someone could give me a working code for this.


    I hope this makes sense. Thank you again for your help.

    Re: Loop Until Cell is Empty


    I would imagine it was something similar to this (from another thread)


    Sub Loop1()' This loop runs until there is nothing in the next column Do ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])" ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, 1))End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login