Calculate Angle

[sanju]

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.

 

[Marc L]

Hello, according to your attachment and Excel / VBA basics this is the way :​

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]

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:

=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.

 

[sanju]

Hello, according to your attachment and Excel / VBA basics this is the way :​

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]

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:

=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]

replace result in column "M" instead of column "F"

Just replace both .Item(2) with .Item(9) …​

​

 

[sanju]

Just replace both .Item(2) with .Item(9) …​

​

Thank you very much!!!