loop, do until (do while?)

[anxoperez]

I need to keep to loop this statement while the condition is True.

I understand that maybe a DO UNTIL NOT TRUE statement might do it?

Any other suggestions.

Either case, I don't know to do a LOOP STATEMENT and what I have been reading up on doesn't help.

Can someone help me?

If ActiveCell.Value < Range("A5").Value Then
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=R[-1]C-R5C2"

 

[Norie]

Hard to tell really since we don't know how you are incrementing the ActiveCell.

Could be something like this.

While ActiveCell.Value<RANGE("A5").VALUE< p> <= Range("A5").Value    
    ActiveCell.Offset(1,0).FormulaR1C1 = "=R[-1]C+1"
    ActiveCell.Offset(0, 1).FormulaR1C1 = "=R[-1]C-R5C2"
    ActiveCell.Offset(1).Select
Wend

Note this probably isn't what you want and also using ActiveCell/Select aren't really good ideas.

I think in your other thread you mentioned the code was to fill in dates.

Care to expand on that and post some sample data?

 

[anxoperez]

What do you mean by "know how I am incrementing the ActiveCell"?

Your formula actually works, but you are right. I am making a mistake because when I mean the active cell I am referring only to the DATE column, not the REGISTRY (POSITION) column. Even though now it works, I would inevitably run into trouble in the future.

How can I fix it?

As you rightly stated, I am trying to fill in the missing dates and the missing registries (positions) for each date.

Here's the table:<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis...[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Norie]

What I mean is once you've dealt with the current active cell where do you move onto?

The code I posted just moves to the next cell down, and like I said that might not be what you want.

And I'm afraid to say it isn't really clear from the data you posted what you want to do.

 

[anxoperez]

this is tough. haha.

well, well. Let me try it again to see if I find the right words.

I want the macro to fill in the missing dates. That means that if the last date I have a record for is 6-June (and it's corresponding position (-1)), then I want it to move down (yes, like you did) and record the next date (7-June) with its corresponding position, which given the formula should be D6 - 1, which should equal -2.

Given that 7-June is less that today, the formula should loop in order to type the entry for 8-june and so on.

is it clearer now?

thanks

 

[Norie]

Something like this perhaps?

Set rng = Range("C5")
While rng.Value<RANGE("A5").VALUE< p> <= Range("A5").Value    
    rng.Offset(1,0).FormulaR1C1 = "=R[-1]C+1"
    rng.Offset(0, 1).FormulaR1C1 = "=R[-1]C-R5C2"
    set rng = rng.Offset(1)
Wend

 

[anxoperez]

Amazing! It works great! I had to do a couple of adjustments but it is exactly what I was looking for.

I thank you inmensely (especially since I had to wait for several weeks to get my answer...)