Formula not working without selecting a cell reference

stirlingmw

Board Regular
Joined
Feb 18, 2013
Messages
75
Morning all

I am having trouble with 2 formulas that calculate range and bearing from latitudes and longitudes. Currently I have the formula in 2 cells on sheet1 (L38 - Range), L39 - Bearing).

L38 = IFERROR(distvincenty(signit(K26),signit(K27),signit(K33),signit(K34)),"--")
L39 = IFERROR(MROUND(MOD(DEGREES(ATAN2(COS(signit(K26)*PI()/180)*SIN(signit(K33)*PI()/180)-SIN(signit(K26)*PI()/180)*COS(signit(K33)*PI()/180)*COS(signit(K34)*PI()/180-signit(K27)*PI()/180),SIN(signit(K34)*PI()/180-signit(K27)*PI()/180)*COS(signit(K26)*PI()/180)))+360,360),1),"--")

K26 - Latitude 1, K27 Longitude 1, K33 - Latitude - 2 and K34 - Longitude 2
distvincenty - Public Function
signit - Function
ATAN2 - Public Function

The issue I am having is that to get these 2 formulas to function I have to unprotect the worksheet click into the formula, select a cell reference and then press return. I have to do this for both formula. Is there any way I can write these formula into a vba worksheet change function. Other people use this workbook and I need to have it password protected, but still need to the function to work.

thanks

Steve
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

stirlingmw

Board Regular
Joined
Feb 18, 2013
Messages
75
Think I have sorted it. Its not elegant coding, but it seems to be working.

VBA Code:
If Not Intersect(Target, Range("K24,L24,M24,N24,K25,L25,M25,N25,K31,L31,M31,N31,K32,L32,M32,N32")) Is Nothing Then
Sheet1.Unprotect
    If Sheet1.Range("K24") <> "" And Sheet1.Range("L24") <> "" And Sheet1.Range("M24") <> "" And Sheet1.Range("N24") <> "" And _
    Sheet1.Range("K25") <> "" And Sheet1.Range("L25") <> "" And Sheet1.Range("M25") <> "" And Sheet1.Range("N25") <> "" And _
    Sheet1.Range("K31") <> "" And Sheet1.Range("L31") <> "" And Sheet1.Range("M31") <> "" And Sheet1.Range("N31") <> "" And _
    Sheet1.Range("K32") <> "" And Sheet1.Range("L32") <> "" And Sheet1.Range("M32") <> "" And Sheet1.Range("N32") <> "" Then
    Sheet1.Range("K26").Value = Sheet1.Range("K24").Value & "°" & Sheet1.Range("L24").Value & "'" & Sheet1.Range("M24").Value & Sheet1.Range("N24").Value
    Sheet1.Unprotect
    Sheet1.Range("K27").Value = Sheet1.Range("K25").Value & "°" & Sheet1.Range("L25").Value & "'" & Sheet1.Range("M25").Value & Sheet1.Range("N25").Value
    Sheet1.Unprotect
    Sheet1.Range("K33").Value = Sheet1.Range("K31").Value & "°" & Sheet1.Range("L31").Value & "'" & Sheet1.Range("M31").Value & Sheet1.Range("N31").Value
    Sheet1.Unprotect
    Sheet1.Range("K34").Value = Sheet1.Range("K32").Value & "°" & Sheet1.Range("L32").Value & "'" & Sheet1.Range("M32").Value & Sheet1.Range("N32").Value
    Sheet1.Unprotect
    Sheet1.Range("J37").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(distvincenty(signit(R[-11]C[1]),signit(R[-10]C[1]),signit(R[-4]C[1]),signit(R[-3]C[1])),""--"")"
    Sheet1.Unprotect
    Sheet1.Range("K37").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(MROUND(MOD(DEGREES(ATAN2(COS(signit(R[-11]C)*PI()/180)*SIN(signit(R[-4]C)*PI()/180)-SIN(signit(R[-11]C)*PI()/180)*COS(signit(R[-4]C)*PI()/180)*COS(signit(R[-3]C)*PI()/180-signit(R[-10]C)*PI()/180),SIN(signit(R[-3]C)*PI()/180-signit(R[-10]C)*PI()/180)*COS(signit(R[-11]C)*PI()/180)))+360,360),1),""--"")"
    Range("K38").Select
        End If
End If
Sheet1.Protect

Basically the code checks to make sure that my deg, min and sec input boxes are not blank (wont fire if blank) it then concatenates the 2 latitudes and longitudes into cells K26, K27 (lat/long 1) and K33, K34 (lat/long 2), it then selects my "Range" cell (J37) and adds the correct formula and then does the same for the "Bearing" cell.

the only issue I have is that somewhere in the other code the sheet is protected, so i have to unprotect the sheet at each step. It doesnt visually slow the code down, so i am content to leave it as is for now.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,450
Messages
5,624,831
Members
416,058
Latest member
SEJTurner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top