# Calculate Angle

#### sanju

##### Member
Hello All,
I want to calculate from "TP" to "TP" from the code shown in column "E".
I have started to build the code but I have failed in it, I am no expert in writing code but I am still learning. can someone help?

I really appreciate any help.

#### Attachments

• 16.9 KB Views: 6

#### Marc L

##### Excel Ninja
Hello, according to your attachment and Excel / VBA basics this is the way :​
Code:
``````Sub Demo1()
Const F = "DEGREES(ATAN2(C#-C¤,B#-B¤))+(B#-B¤<0)*360"
Dim Rf As Range, R&, V, L&
With Sheet1.[A1].CurrentRegion.Columns(5)
Set Rf = .Find("TP", , , 1):  If Rf Is Nothing Then Beep: Exit Sub
R = Rf.Row
V = .Item(2).Value2
Set Rf = .FindNext(Rf)
While Rf.Row > R
V(R, 1) = .Parent.Evaluate(Replace(Replace(F, "#", Rf.Row), "¤", R))
For L = R + 1 To Rf.Row + (Rf.Row < .Rows.count):  V(L, 1) = V(R, 1):  Next
R = Rf.Row
Set Rf = .FindNext(Rf)
Wend
Set Rf = Nothing
.Item(2).Value2 = V
End With
End Sub``````
Do you like it ? So thanks to click on bottom right Like !​

#### p45cal

##### Well-Known Member
Which version of Excel do you have? Do you have the FILTER function available to you on the worksheet?
If so, the attached contains a formula approach.
Column U is the final formula, you don't need the stuff in columns O to S, which just show my workings to get to a single formula in column S. Column U is just Column S expanded into a formula which only refers to the raw data (no 'helper' columns).
You can also, of course, filter only for TP (or delete the formulae which aren't on a TP row).

That formula for any cell in row 2:
Code:
``=IFERROR(DEGREES(ATAN2(INDEX(FILTER(\$C2:\$C\$31,\$E2:\$E\$31=\$E2),2)-INDEX(FILTER(\$C2:\$C\$31,\$E2:\$E\$31=\$E2),1),INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),2)-INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),1)))+IF(INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),2)-INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),1)<0,360),"no pair below")``
copied down.

#### Attachments

• 20.4 KB Views: 2

#### sanju

##### Member
Hello, according to your attachment and Excel / VBA basics this is the way :​
Code:
``````Sub Demo1()
Const F = "DEGREES(ATAN2(C#-C¤,B#-B¤))+(B#-B¤<0)*360"
Dim Rf As Range, R&, V, L&
With Sheet1.[A1].CurrentRegion.Columns(5)
Set Rf = .Find("TP", , , 1):  If Rf Is Nothing Then Beep: Exit Sub
R = Rf.Row
V = .Item(2).Value2
Set Rf = .FindNext(Rf)
While Rf.Row > R
V(R, 1) = .Parent.Evaluate(Replace(Replace(F, "#", Rf.Row), "¤", R))
For L = R + 1 To Rf.Row + (Rf.Row < .Rows.count):  V(L, 1) = V(R, 1):  Next
R = Rf.Row
Set Rf = .FindNext(Rf)
Wend
Set Rf = Nothing
.Item(2).Value2 = V
End With
End Sub``````
Do you like it ? So thanks to click on bottom right Like !​
@Marc L
The lisp you write is great. It really works well!!
Can you give me replace result in column "M" instead of column "F" I am having little difficulty to understand your code so I am not able to replace result.

Thanks a lot for the help.

#### sanju

##### Member
Which version of Excel do you have? Do you have the FILTER function available to you on the worksheet?
If so, the attached contains a formula approach.
Column U is the final formula, you don't need the stuff in columns O to S, which just show my workings to get to a single formula in column S. Column U is just Column S expanded into a formula which only refers to the raw data (no 'helper' columns).
You can also, of course, filter only for TP (or delete the formulae which aren't on a TP row).

That formula for any cell in row 2:
Code:
``=IFERROR(DEGREES(ATAN2(INDEX(FILTER(\$C2:\$C\$31,\$E2:\$E\$31=\$E2),2)-INDEX(FILTER(\$C2:\$C\$31,\$E2:\$E\$31=\$E2),1),INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),2)-INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),1)))+IF(INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),2)-INDEX(FILTER(\$B2:\$B\$31,\$E2:\$E\$31=\$E2),1)<0,360),"no pair below")``
copied down.
@p45cal
Thanks for giving the formula and joining this thread, but I want the result in VBA code, because the VBA code to get the result doesn't hang the excel sheet while this formula may be in long row.

#### Marc L

##### Excel Ninja
replace result in column "M" instead of column "F"
Just replace both `.Item(2)` with `.Item(9)` …​

#### sanju

##### Member
Just replace both `.Item(2)` with `.Item(9)` …​
Thank you very much!!!