Coding Help for Range Calculations

[AK_Beaver]

I'm trying to write a VBA code that calculates mean bearing of a group of bearings and distances. The Input needs to be a Range.



Bearing
Distance


N 80-05-36 E
664.62


S 88-58-12 E
2640.00


S 88-58-12 E
2517.90



I already have a code that converts the bearings to Azimuths in decimal degrees (i.e. N 88-00-00 E = 88.00000) and I get the overall idea of what I need to do. Each Bearing needs to be multiplied by the distance and this is done for each row. Those are added together and then divided by the sum of all the distances. My question is...Is their a way to write the code so that I don't need to call a specific cell for the calculations. For example: I don't want to have to say like
((A1*A2)+(B1*B2))/(B1+B2). I'm hoping to be able to write out the bearings and distances in any two columns on my sheet, input the range into the function and have the mean bearing output be calculated. If you can help steer me in the right direction, I'd appreciate it.

[SamT]

What does "Mean Bearing" mean? Is it the bearing from start point to end point?

Also, please explain how to interpret a Bearing in the form "N/S compass reading E/W"

[SamT]

Is their a way to write the code so that I don't need to call a specific cell for the calculations.This code goes in a standard module

You'll have to fill in the blanks, (Bearing2Azimuth,)

Public Function AK_Beaver(Inputs As Range) As 'Unknown, Fill in the Blanks
Dim rw As Range
Dim Dividend As Double
Dim Divisor As Double
Dim Result as double

For Each rw In Inputs.Rows
Dividend = Dividend + Bearing2Azimuth(rw.Cells(1)) * rw.Cells(2)
Divisor = Divisor + rw.Cells(2)
Next

Result = Dividend / Divisor

NewResult = 'Fill in the blanks here

AK_Beaver = NewResult
End Function

To use, In your results cell, enter the formula "=AK_Beaver(Select your range here)"

[Paul_Hossler]

My question is...Is their a way to write the code so that I don't need to call a specific cell for the calculations.

For example: I don't want to have to say like ((A1*A2)+(B1*B2))/(B1+B2).

I'm hoping to be able to write out the bearings and distances in any two columns on my sheet, input the range into the function and have the mean bearing output be calculated.

If you can help steer me in the right direction, I'd appreciate it.

"Steer me" :rotflmao:

I 'think' you want a VBA User Defined Function (UDF) that returns a Double that you can use in a worksheet


Function AZ (R as Range) As Double

You example has 3 rows, but you example formula only uses 2 (1 and 2) so I'm not sure about what you want back

Provide more detail and the calculations if you can

[AK_Beaver]

Thanks everyone for your help. SamT, that did the trick. I was getting an error message at first and was about to post for more help, but I told myself to keep trying and low and behold I debugged the problem! I'm getting better that's for sure. I am extremely thankful for this forum, that's for sure.

[SamT]

:trophy: