 Highlighted
Deleted
Not applicable

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

7 Replies
Highlighted

# Re: X+1 +1 +1... till match a condition

Hello,

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.

Highlighted

# Re: X+1 +1 +1... till match a condition

Sry for my bad english :S

Hope now u can understand with this examples Highlighted

# Re: X+1 +1 +1... till match a condition

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.

Step back from the table and explain what you need to achieve. What is the bigger picture?

Highlighted

# Re: X+1 +1 +1... till match a condition

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

Highlighted

# Re: X+1 +1 +1... till match a condition

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

# Re: X+1 +1 +1... till match a condition

Wow this works rly more faster than my macro xD

Thanks for the code, but my question is whether it is possible to do this without macros

```Sub Botón1_Haga_clic_en()
Range("D4:D33").ClearContents
Do While Hoja1.Cells(4, 11) < Hoja1.Cells(4, 3)
Hoja1.Cells(4, 4) = Hoja1.Cells(4, 4) + 1
Loop
Do While Hoja1.Cells(5, 11) < Hoja1.Cells(5, 3)
Hoja1.Cells(5, 4) = Hoja1.Cells(5, 4) + 1
Loop
Do While Hoja1.Cells(6, 11) < Hoja1.Cells(6, 3)
Hoja1.Cells(6, 4) = Hoja1.Cells(6, 4) + 1
Loop
Do While Hoja1.Cells(7, 11) < Hoja1.Cells(7, 3)
Hoja1.Cells(7, 4) = Hoja1.Cells(7, 4) + 1
Loop
Do While Hoja1.Cells(8, 11) < Hoja1.Cells(8, 3)
Hoja1.Cells(8, 4) = Hoja1.Cells(8, 4) + 1
Loop
Do While Hoja1.Cells(9, 11) < Hoja1.Cells(9, 3)
Hoja1.Cells(9, 4) = Hoja1.Cells(9, 4) + 1
Loop
Do While Hoja1.Cells(10, 11) < Hoja1.Cells(10, 3)
Hoja1.Cells(10, 4) = Hoja1.Cells(10, 4) + 1
Loop
Do While Hoja1.Cells(11, 11) < Hoja1.Cells(11, 3)
Hoja1.Cells(11, 4) = Hoja1.Cells(11, 4) + 1
Loop
Do While Hoja1.Cells(12, 11) < Hoja1.Cells(12, 3)
Hoja1.Cells(12, 4) = Hoja1.Cells(12, 4) + 1
Loop
Do While Hoja1.Cells(13, 11) < Hoja1.Cells(13, 3)
Hoja1.Cells(13, 4) = Hoja1.Cells(13, 4) + 1
Loop

End Sub```
Highlighted

# Re: X+1 +1 +1... till match a condition

Not without macros, no. For calculating an individual cell you can use Goal Seek in the Data > Analysis ribbon, but for many cells like your scenario, you will need VBA.