VBA loop until condition is met

[cidfidou]

Hi Excel gods,

I have been trying in vain to create a simple loop but as you can already guess my VBA skills are quite limited.

I am trying to run a macro that will incrementally populate each cell in a range (cells F51 to F69) from 0 to X until another calculation depending on each Value per cell in the range F51 to F69 will give a min value of 90 in the range (J51 to F69).

Hope this is clear.

thanks in advcnae

 

[Joe4]

Hope this is clear.

I am afraid it is not (at least not to me).

Can you walk us through an actual example of how it should work?

Also, there are tools you can use to post screen images (if you think that might help in your explanation). They are listed in Section B of this link here: Guidelines for Forum Use.
There is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[cidfidou]

Hi Joe,

thanks for coming back to me.

I am going to try to explain it with a new example as I think I could modify the code to suit my needs,

Let say we have 3 columns starting in A (# of items sold in col A / Price in col B and Total sales in Col C2 = A2*B2)

I am looking for a VBA code that will populate every cell in col A until the total sales is at least 90.

So, if in line 2, the price is 10 I would like 9
in line 3, the price is 8, I would like 12 and so on

It might not make sense with this example as I could use a simple formula to find the result but I would need a loop until code in order to do that on a complicated spreadsheet - the goal is to use an iteration process starting at 0 to X until I get 90 per line

Hope this is clearer

 

[Joe4]

So, where will this "90" number be coming from?
Is it pulling from somewhere on the sheet?
Or do you want the user to be prompted by VBA code to input the desired target value?

 

[MrTeeny]

Must admit it's not the clearest but I'm assuming you want the value to calculate the value equal to or greater than 90 in column C using data from column B and whole numbers? if so maybe something like this might work for you, obviously change references as needed but I'll assume we go from B1 downwards being populated . No idea if that's what you were after and you'd need to add error trapping if blank cells in your range

Sub calc90()
Dim i As Integer
Lrow = Range("B100").End(xlUp).Row ' last row with data, change it if you'll have more than 100 filled cells


For i = 1 To Lrow


Range("A" & i).Value = Application.RoundUp((90 / Range("B" & i).Value), 0)
Range("C" & i).Value = Range("A" & i).Value * Range("B" & i).Value


Next i


End Sub

 

[Roxxien]

Your solution is kind of a good one MrTeeny.

However, what I think he is searching is how to do a loop until a certain condition.

In that case, the way to would talk you the most is this.

Dim itest as Integer
Dim irow as Integer

Do until itest = 90
irow=irow+1
'Do want you want
'calculate the new itest
itest=Cells(irow,1)*Cells(irow,2)*3.14159
Loop

I can say it, I wrote anything that goes through my head in the loop.

But, the main idea is that this will loop any number of row until it reach the row that equal 90. This is what you seems to be searching.

WARNING : BE CAREFUL, AN INCORRECT CRITERIA CAN CREATE INFINITE LOOP, use ctrl+break to stop your macro if problem happen

 

[Joe4]

MrTeeny,

Yes, that is where I was going with that. But I was pretty certain that they do not want 90 hard-coded in their code!

By the way, you can change this line:

[COLOR=#333333]Lrow = Range("B100").End(xlUp).Row[/COLOR]

to this:

[COLOR=#333333]Lrow = Cells(Rows.Count,"B").End(xlUp).Row[/COLOR]

.
and then you will never have to worry about touching/adjusting it ever.

 

[Joe4]

However, what I think he is searching is how to do a loop until a certain condition.

In that case, the way to would talk you the most is this.

I can say it, I wrote anything that goes through my head in the loop.


But, the main idea is that this will loop any number of row until it reach the row that equal 90. This is what you seems to be searching.

If the goal is to just see how to iterate, that is fine.

But if the problem is truly as was presented in the example, just trying to find the number in each row that would put the column c to 90 or above, iterating is a very slow, inefficient, and unnecessary way to do it. The ROUNDUP method is much more effective, and avoids having to do unnecessary loops.

 

[cidfidou]

Thanks all for the brilliant answers.. in relation to the 90 I can use the hard coded version or have a 90 per line in the spreadsheet or in one cell...I will test it and come back if any questions... Thanks to all and sorry for not being clear in the first instance but i am not an english speaker...Thanks again as ur help is much appreciated

 

[MrTeeny]

MrTeeny,

Yes, that is where I was going with that. But I was pretty certain that they do not want 90 hard-coded in their code!

By the way, you can change this line:

[COLOR=#333333]Lrow = Range("B100").End(xlUp).Row[/COLOR]

to this:

[COLOR=#333333]Lrow = Cells(Rows.Count,"B").End(xlUp).Row[/COLOR]

.
and then you will never have to worry about touching/adjusting it ever.

Thanks , much simpler than my version, I just went with the 90 as he said sales need to be at least that figure, it can always be coded into a separate cell if needed. Was more a case of just throwing some starting code out there to see if it was in line with what he wanted tbbh