# Formula not working without selecting a cell reference

#### stirlingmw

##### Board Regular
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

### 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
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),signit(R[-10]C),signit(R[-4]C),signit(R[-3]C)),""--"")"
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.

Replies
6
Views
212
Replies
5
Views
879
Replies
5
Views
219
Replies
4
Views
505
Replies
2
Views
314

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

### Share this page ### 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