X+1 +1 +1... till match a condition

Question

Hi,
I have this table that needs to do a formula on:
J3 of J2+x till B3&gt;=H3.
J4 of J3+x till B4&gt;=H4.
J5 of J4+x till B5&gt;=H5.
...
&nbsp;
Is this possible without macros?

ingeborghawighorst · Answer

Hello,
&nbsp;
what is "J3 of J2"? Can you manually mock up a few examples of what you want to achieve in a spreadsheet and post that workbook? Then explain the logic.&nbsp;

ingeborghawighorst · Answer

H3 has a formula that refers to J3. You can't now put a formula into J3 that refers to H3. That is a circular reference.
&nbsp;
Step back from the table and explain what you need to achieve. What is the bigger picture?

ingeborghawighorst · Answer

I see now.&nbsp;
&nbsp;
For a single calculation you could use this macro approach:
&nbsp;
Sub testOne()
Dim i As Long

Application.ScreenUpdating = False

i = Selection.Row
While Range("B" &amp; i) &gt; Range("H" &amp; i)
    Range("J" &amp; i) = Range("J" &amp; i) + 1
Wend

Application.ScreenUpdating = True

End Sub

If you want to perform the calculation on all rows of the table, you can loop through all rows, but due to the exponential nature of the results, each row will take twice as long as the previous row to calculate and that will add up to a loooooong time.
&nbsp;
Sub testAllRows()
Dim i As Long, J As Long
Dim lastrow As Long

lastrow = Cells(Rows.Count, 2).End(xlUp).Row ' the last row with a number in column B

For i = 3 To lastrow
Application.ScreenUpdating = False
    While Range("B" &amp; i) &gt; Range("H" &amp; i)
        Range("J" &amp; i) = Range("J" &amp; i) + 1
    Wend
Application.ScreenUpdating = True
Next i

End Sub

deleted · Answer

Sry for my bad english :S
Hope now u can understand with this examples

deleted · Answer

I know that this is a circular reference, for this i ask if is this possible without macros.
What i have to archive in J3 is&nbsp;go adding up (1+1+1..)until K3 mark o (need add more +1... when mark x)
the same with the rest of cells of J respectively
sometimes I have to add 1 + 1 + 1 + 1 ... more than 200 times manually

Forum Discussion

X+1 +1 +1... till match a condition

Share

Resources