Forum Discussion

Deleted

Dec 20, 2017

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

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

Deleted

Dec 21, 2017

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 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

IngeborgHawighorst

MVP

Dec 21, 2017

I see now.

For a single calculation you could use this macro approach:

Sub testOne()
Dim i As Long

Application.ScreenUpdating = False

i = Selection.Row
While Range("B" & i) > Range("H" & i)
    Range("J" & i) = Range("J" & 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.

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" & i) > Range("H" & i)
        Range("J" & i) = Range("J" & i) + 1
    Wend
Application.ScreenUpdating = True
Next i


End Sub