• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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