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

%3CLINGO-SUB%20id%3D%22lingo-sub-138909%22%20slang%3D%22en-US%22%3EX%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-138909%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20have%20this%20table%20that%20needs%20to%20do%20a%20formula%20on%3A%3C%2FP%3E%0A%3CP%3EJ3%20of%20J2%2Bx%20till%20B3%26gt%3B%3DH3.%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EJ4%20of%20J3%2Bx%20till%20B4%26gt%3B%3DH4.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EJ5%20of%20J4%2Bx%20till%20B5%26gt%3B%3DH5.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E...%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20this%20possible%20without%20macros%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-138909%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E%2B1%20%2B1%20%2B1%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eequation%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EX%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139736%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139736%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20without%20macros%2C%20no.%20For%20calculating%20an%20individual%20cell%20you%20can%20use%20Goal%20Seek%20in%20the%20Data%20%26gt%3B%20Analysis%20ribbon%2C%20but%20for%20many%20cells%20like%20your%20scenario%2C%20you%20will%20need%20VBA.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139373%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139373%22%20slang%3D%22en-US%22%3E%3CP%3EWow%20this%20works%20rly%20more%20faster%20than%20my%20macro%20xD%3C%2FP%3E%0A%3CP%3EThanks%20for%20the%20code%2C%20but%20my%26nbsp%3Bquestion%20is%20whether%20it%20is%20possible%20to%20do%20this%20without%20macros%3C%2FP%3E%0A%3CPRE%3ESub%20Bot%C3%B3n1_Haga_clic_en()%0A%20%20%20%20Range(%22D4%3AD33%22).ClearContents%0A%20%20%20%20Do%20While%20Hoja1.Cells(4%2C%2011)%20%26lt%3B%20Hoja1.Cells(4%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(4%2C%204)%20%3D%20Hoja1.Cells(4%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(5%2C%2011)%20%26lt%3B%20Hoja1.Cells(5%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(5%2C%204)%20%3D%20Hoja1.Cells(5%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(6%2C%2011)%20%26lt%3B%20Hoja1.Cells(6%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(6%2C%204)%20%3D%20Hoja1.Cells(6%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(7%2C%2011)%20%26lt%3B%20Hoja1.Cells(7%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(7%2C%204)%20%3D%20Hoja1.Cells(7%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(8%2C%2011)%20%26lt%3B%20Hoja1.Cells(8%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(8%2C%204)%20%3D%20Hoja1.Cells(8%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(9%2C%2011)%20%26lt%3B%20Hoja1.Cells(9%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(9%2C%204)%20%3D%20Hoja1.Cells(9%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(10%2C%2011)%20%26lt%3B%20Hoja1.Cells(10%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(10%2C%204)%20%3D%20Hoja1.Cells(10%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(11%2C%2011)%20%26lt%3B%20Hoja1.Cells(11%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(11%2C%204)%20%3D%20Hoja1.Cells(11%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(12%2C%2011)%20%26lt%3B%20Hoja1.Cells(12%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(12%2C%204)%20%3D%20Hoja1.Cells(12%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%20%20%20%20Do%20While%20Hoja1.Cells(13%2C%2011)%20%26lt%3B%20Hoja1.Cells(13%2C%203)%0A%20%20%20%20%20%20%20%20Hoja1.Cells(13%2C%204)%20%3D%20Hoja1.Cells(13%2C%204)%20%2B%201%0A%20%20%20%20Loop%0A%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139366%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139366%22%20slang%3D%22en-US%22%3E%3CP%3EI%20see%20now.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20a%20single%20calculation%20you%20could%20use%20this%20macro%20approach%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESub%20testOne()%0ADim%20i%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0Ai%20%3D%20Selection.Row%0AWhile%20Range(%22B%22%20%26amp%3B%20i)%20%26gt%3B%20Range(%22H%22%20%26amp%3B%20i)%0A%20%20%20%20Range(%22J%22%20%26amp%3B%20i)%20%3D%20Range(%22J%22%20%26amp%3B%20i)%20%2B%201%0AWend%0A%0AApplication.ScreenUpdating%20%3D%20True%0A%0AEnd%20Sub%0A%3C%2FPRE%3E%0A%3CP%3EIf%20you%20want%20to%20perform%20the%20calculation%20on%20all%20rows%20of%20the%20table%2C%20you%20can%20loop%20through%20all%20rows%2C%20but%20due%20to%20the%20exponential%20nature%20of%20the%20results%2C%20each%20row%20will%20take%20twice%20as%20long%20as%20the%20previous%20row%20to%20calculate%20and%20that%20will%20add%20up%20to%20a%20loooooong%20time.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESub%20testAllRows()%0ADim%20i%20As%20Long%2C%20J%20As%20Long%0ADim%20lastrow%20As%20Long%0A%0Alastrow%20%3D%20Cells(Rows.Count%2C%202).End(xlUp).Row%20'%20the%20last%20row%20with%20a%20number%20in%20column%20B%0A%0AFor%20i%20%3D%203%20To%20lastrow%0AApplication.ScreenUpdating%20%3D%20False%0A%20%20%20%20While%20Range(%22B%22%20%26amp%3B%20i)%20%26gt%3B%20Range(%22H%22%20%26amp%3B%20i)%0A%20%20%20%20%20%20%20%20Range(%22J%22%20%26amp%3B%20i)%20%3D%20Range(%22J%22%20%26amp%3B%20i)%20%2B%201%0A%20%20%20%20Wend%0AApplication.ScreenUpdating%20%3D%20True%0ANext%20i%0A%0A%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139278%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139278%22%20slang%3D%22en-US%22%3E%3CP%3EI%20know%20that%20this%20is%20a%20circular%20reference%2C%20for%20this%20i%20ask%20if%20is%20this%20possible%20without%20macros.%3C%2FP%3E%0A%3CP%3EWhat%20i%20have%20to%20archive%20in%20J3%20is%26nbsp%3Bgo%20adding%20up%20(1%2B1%2B1..)until%20K3%20mark%20o%20(need%20add%20more%20%2B1...%20when%20mark%20x)%3C%2FP%3E%0A%3CP%3Ethe%20same%20with%20the%20rest%20of%20cells%20of%20J%20respectively%3C%2FP%3E%0A%3CP%3Esometimes%20I%20have%20to%20add%201%20%2B%201%20%2B%201%20%2B%201%20...%20more%20than%20200%20times%20manually%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139164%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139164%22%20slang%3D%22en-US%22%3E%3CP%3EH3%20has%20a%20formula%20that%20refers%20to%20J3.%20You%20can't%20now%20put%20a%20formula%20into%20J3%20that%20refers%20to%20H3.%20That%20is%20a%20circular%20reference.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%20back%20from%20the%20table%20and%20explain%20what%20you%20need%20to%20achieve.%20What%20is%20the%20bigger%20picture%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139123%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139123%22%20slang%3D%22en-US%22%3E%3CP%3ESry%20for%20my%20bad%20english%20%3AS%3C%2FP%3E%0A%3CP%3EHope%20now%20u%20can%20understand%20with%20this%20examples%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F25973iAC931B8EAD353CAD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Captura%20de%20pantalla%20(10).png%22%20title%3D%22Captura%20de%20pantalla%20(10).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-139094%22%20slang%3D%22en-US%22%3ERe%3A%20X%2B1%20%2B1%20%2B1...%20till%20match%20a%20condition%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-139094%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhat%20is%20%22J3%20of%20J2%22%3F%20Can%20you%20manually%20mock%20up%20a%20few%20examples%20of%20what%20you%20want%20to%20achieve%20in%20a%20spreadsheet%20and%20post%20that%20workbook%3F%20Then%20explain%20the%20logic.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Deleted
Not applicable

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

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

Sry for my bad english :S

Hope now u can understand with this examples

Captura de pantalla (10).png

Highlighted

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

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

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

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

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.