read sheet 1 display on sheet 7 (Phytagoras)

[montecarlo2012]

Sub Pytagoras_two()

Dim j As Integer, k As Integer

For j = 2 To 6
    For k = 2 To 10180
    
    
    Cells(k, j + 9).Formula = "=int(trunc(SQRT(" & Cells(k, j).Address & "^2 + " & Cells(k + 1, j).Address & "^2)))"
        If Cells(k, j + 9).Formula > 36 Then
        Cells(k, j + 9).Formula = "=abs(int(trunc(SQRT(" & Cells(k, j).Address & "^2 + " & Cells(k + 1, j).Address & "^2)))-36)"
        End If
    '
    Next
Next


End Sub

I've been working on, and while it does what I need it to do, it's not very efficient and takes quite a long time to execute. I'm looking for some assistance in optimizing it.
The purpose of this macro is to perform calculations on a range of cells from sheet 1 (B2:F) and display the results on sheet 7 (B2:F). However, it takes more than a minute to complete.

I'm seeking advice on how to optimize this code to make it run faster. Also, I would like to ensure that it operates on the specified sheets and range (sheet 1 array at B2:F and displays results on sheet 7 B2:F).

Thank you all for taking the time to read this and for any suggestions or guidance you can provide to help me improve the efficiency of this VBA macro.

 

[PeteWright]

I haven't checked yet what your macro does, but wouldn't it be better to do the calculations in VBA instead of placing a formula within the sheet?
Writing something into a cell always takes time.
Could you explain in a few words what your macro should do and maybe give a short example or some data to work with?

Without data I cannot see, what your code does.

 

[montecarlo2012]

PeteW. I got this already thanks.

 

[jolivanes]

@montecarlo2012
Would you be so kind to show your fix so people searching for a solution, like you did, for a same or similar problem might benefit from it like you would have done.
I am sure you can see the frustration if you tried for a long time to fix something and then start looking for a solution on the net, find what you think to be a solution and the answer is "I got it."

 

[montecarlo2012]

Sub theory_ne()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim A As Double, B As Double, Z As Double
    Dim i As Integer, j As Integer
    Dim ZZ As Double, sqrt As Double
    
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet7")
    
    ZZ = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    
    For i = 2 To 6
        For j = 2 To ZZ
            Z = Int(Sqr(ws1.Cells(j, i).Value ^ 2 + ws1.Cells(j, i + 1).Value ^ 2))
            
                If Z > 36 Then
                   Z = Z - 36
                End If
                       
            ws2.Cells(j, i).Value = Z
            
        Next j
    Next i
    
End Sub

 

[bebo021999]

If you want to upgrade your code, please note that your current code is calculating and directly copying data onto cells.
If the data is large enough, it will slow down processing speed.
Always remember that storing your data in an array, performing computations on it, saving the results in another array (results array), and then pasting those results back into the sheet can significantly optimize processing speed.
Like this:

Option Explicit
Sub theory_ne()
Dim i&, j&, ZZ&, ws1 As Worksheet, ws2 As Worksheet, res(), data
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet7")
ZZ = ws1.Cells(Rows.Count, "B").End(xlUp).Row ' last use row
data = ws1.Range("B2:G" & ZZ).Value ' variable contains data need to be calculated
ReDim res(1 To UBound(data), 1 To UBound(data, 2) - 1) ' a results 2D array
For j = 1 To UBound(data, 2) - 1 ' loop from col B to F
    For i = 1 To UBound(data)
        res(i, j) = Int(Sqr(data(i, j) ^ 2 + data(i, j + 1) ^ 2)) Mod 36 ' store into results
    Next
Next
ws2.Range("B2").Resize(UBound(data), UBound(data, 2) - 1).Value = res ' write results back to sheet
End Sub