Cells(2, i).Formula = "=trunc(SQRT(" & Cells(2, j).Address & "^2 + " & Cells(3, j).Address & "^2))"

[montecarlo2012]

Hi everyone.
This question has to do with programming logic,
Outer loop and inner loop how to decide wich variable will be where and why.
In this presentation I can see clearly what change (variables) and constants.
so how to process this idea, how to pick inner and outer.

Cells(2, 43).Formula = "=trunc(SQRT(" & Cells(2, 2).Address & "^2 + " & Cells(3, 2).Address & "^2))"

Cells(2, 44).Formula = "=trunc(SQRT(" & Cells(2, 3).Address & "^2 + " & Cells(3, 3).Address & "^2))"

Cells(2, 45).Formula = "=trunc(SQRT(" & Cells(2, 4).Address & "^2 + " & Cells(3, 4).Address & "^2))"

Cells(2, 46).Formula = "=trunc(SQRT(" & Cells(2, 5).Address & "^2 + " & Cells(3, 5).Address & "^2))"

Cells(2, 47).Formula = "=trunc(SQRT(" & Cells(2, 6).Address & "^2 + " & Cells(3, 6).Address & "^2))"

so applying the idea mentioned before I come out with

Sub Logic1()
Dim I As Integer
Dim J As Integer

For I = 43 To 47
For J = 2 To 6
Cells(2, I).Formula = "=trunc(SQRT(" & Cells(2, J).Address & "^2 + " & Cells(3, J).Address & "^2))"
Next J
Next I

End Sub

and,,, do not give me the results I was expecting, give me some results but wrong.

Book1
	B	C	D	E	F	G
1
2	4	5	19	24	36
3	8	15	18	29	33
4
Sheet1

This the little example
and this is the right answer

Book1
	G	H	I	J	K	L	M	N
1
2		WRONG	48	48	48	48	48
3
4		CORRECT	8	15	26	37	48
5
6
Sheet1

thanks for reading

 

[DanteAmor]

Hi @montecarlo2012.

Thanks for posting on the forum.​

Check my following explanation:​

You don't need 2 cycles. Only one cycle for cells AQ2 to AU2 (cells(2, 43) to cells(2, 47)), that is, the cycle from 43 to 47.
And a counter that starts at 2 and ends at 6, this counter will increment with each cycle, that is, the cycle starts at 43, the counter at 2, then the cycle changes to 44 and the counter to 3, then 45 and 4 , then 46 and 5, and finally 47 and 6.

That would be an option:​

Sub Logic2()
  Dim i As Integer
  Dim j As Integer
  
  j = 2
  For i = 43 To 47
    Cells(2, i).Formula = "=trunc(SQRT(" & Cells(2, j).Address & "^2 + " & Cells(3, j).Address & "^2))"
    j = j + 1
  Next i
End Sub

Another option to put formulas without cycles:​

Sub Logic3()
  With Range("AQ2:AU2")
    .Formula = "=TRUNC(SQRT(B2^2 + B3^2))"
  End With
End Sub

Try and tell me.
Cordially
Dante Amor

 

[montecarlo2012]

Dante, Thanks, great lesson, ||Marked solution already||