If k > 36 Then k = k - 19 End If

[montecarlo2012]

Hello everyone.
I have a piece of code that I'm working on, and I need to add a new condition to it. I've tried a few different approaches, but so far none of them have worked. I'm looking for help with both the code itself and the logic I'm using to try to solve the problem.

This is what I have

Sub L101()

For j = 2 To 6
    For k = 2 To 25
    
        Cells(k, j + 7).Formula = "=trunc(SQRT(" & Cells(k, j).Address & "^2 + " & Cells(k + 1, j).Address & "^2))"
        
        If k > 36 Then
        k = k - 19
        End If
    
    
    Next
Next


End Sub

I tried to create a variable for Cells(k, j + 7).Formula but do not work, so now I am trap in the logic here
how can I control the results

so the code without the condition work, but I need the correction.
this is the results without the condition

ALLCODES.xls
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	8	11	13	14	34			8	11	24	28	43
3	1	3	21	25	27			8	9	25	32	35
4	8	9	14	20	23			8	10	23	31	42
5	4	5	19	24	36			8	15	26	37	48
6	8	15	18	29	33			8	16	22	32	47
7	2	6	14	15	34			5	16	26	35	47
Sheet15

Cell Formulas
Range	Formula
I2	I2	=TRUNC(SQRT($B$2^2 + $B$3^2))
J2	J2	=TRUNC(SQRT($C$2^2 + $C$3^2))
K2	K2	=TRUNC(SQRT($D$2^2 + $D$3^2))
L2	L2	=TRUNC(SQRT($E$2^2 + $E$3^2))
M2	M2	=TRUNC(SQRT($F$2^2 + $F$3^2))
I3	I3	=TRUNC(SQRT($B$3^2 + $B$4^2))
J3	J3	=TRUNC(SQRT($C$3^2 + $C$4^2))
K3	K3	=TRUNC(SQRT($D$3^2 + $D$4^2))
L3	L3	=TRUNC(SQRT($E$3^2 + $E$4^2))
M3	M3	=TRUNC(SQRT($F$3^2 + $F$4^2))
I4	I4	=TRUNC(SQRT($B$4^2 + $B$5^2))
J4	J4	=TRUNC(SQRT($C$4^2 + $C$5^2))
K4	K4	=TRUNC(SQRT($D$4^2 + $D$5^2))
L4	L4	=TRUNC(SQRT($E$4^2 + $E$5^2))
M4	M4	=TRUNC(SQRT($F$4^2 + $F$5^2))
I5	I5	=TRUNC(SQRT($B$5^2 + $B$6^2))
J5	J5	=TRUNC(SQRT($C$5^2 + $C$6^2))
K5	K5	=TRUNC(SQRT($D$5^2 + $D$6^2))
L5	L5	=TRUNC(SQRT($E$5^2 + $E$6^2))
M5	M5	=TRUNC(SQRT($F$5^2 + $F$6^2))
I6	I6	=TRUNC(SQRT($B$6^2 + $B$7^2))
J6	J6	=TRUNC(SQRT($C$6^2 + $C$7^2))
K6	K6	=TRUNC(SQRT($D$6^2 + $D$7^2))
L6	L6	=TRUNC(SQRT($E$6^2 + $E$7^2))
M6	M6	=TRUNC(SQRT($F$6^2 + $F$7^2))
I7	I7	=TRUNC(SQRT($B$7^2 + $B$8^2))
J7	J7	=TRUNC(SQRT($C$7^2 + $C$8^2))
K7	K7	=TRUNC(SQRT($D$7^2 + $D$8^2))
L7	L7	=TRUNC(SQRT($E$7^2 + $E$8^2))
M7	M7	=TRUNC(SQRT($F$7^2 + $F$8^2))

as you can see, the numbers 43 and the others greater than 36 are an error.
thank for reading.

 

[CSmith]

What is the actual logic problem you are trying to solve?
Looks like your loop only provides for cells B2-F26 being calculated at I2-M25
The code looks to calculate c from the formula c^2= a^2 + b^2 using a TRUNC(SQRT())
What is the actual issue here?

 

[Tetra201]

Maybe

Sub Test()
For j = 2 To 7
    For k = 2 To 6
        s = "=TRUNC(SQRT(" & Cells(j, k).Address & "^2 + " & Cells(j + 1, k).Address & "^2))"
        Cells(j, k + 7).Formula = IIf(Evaluate(s) > 36, s & "-19", s)
    Next
Next
End Sub

 

[montecarlo2012]

Thank you Tetra201, work perfect, I already mark as solution, great detail, and new for me.
so with Cells(j, k + 7).Formula = IIf(Evaluate(s) > 36, s & "-19", s) you don't need
if___then__ end if, awesome
where is the theory about this kind of lines.
Thanks
Tetra201

 

[CSmith]

where is the theory about this kind of lines.

Application.Evaluate method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[montecarlo2012]

thanks Smith