cwb1021
03-28-2017, 03:55 PM
Experts,
I'm trying to create a user form that allows the user to input latitude, longitude and a radius, and have the distance between that point and a set of other points calculated. The values will then populate a blank column in the workbook. I have the following code im trying out:
Private Sub CalcDistButton1_Click()
Dim ScanRad As Single
Dim SubLat As Single, SubLon As Single
Dim FilterCell As Range, DataRange As Range
Dim x As Range, y As Range, z As Range
Dim a As Range, b As Range, c As Range
Set DataRange = Intersect(Worksheets("Sheet1").Columns(7), Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible))
'Pull scan radius value from input box
ScanRad = UserForm1.TextBox3.Text
SubLat = UserForm1.TextBox1.Text
SubLon = UserForm1.TextBox2.Text
'Calculate offset distance for unhidden rows
For Each FilterCell In DataRange
x = FilterCell.Offset(, 1)
y = FilterCell.Offset(, 2)
z = FilterCell.Offset(, 4)
'Test Calclulation
'a = x*y
If FilterCell.Value <> "" Then
FilterCell.Offset(, 5) = Application.Acos(Cos(Application.Radians(90 - SubLat)) * Cos(Application.Radians(90 - x)) + Sin(Application.Radians(90 - SubLat)) * _
Sin(Application.Radians(90 - x)) * Cos(Application.Radians(SubLon - y)) * ((z) / 5280))
End If
Next FilterCell
End Sub
My problem is that I'm not sure how to incorporate the values of x,y & z in the for loop. Does this require multiple if statements? I have one other question as well - for passing values from the text boxes in the user form to the procedure, would these need to be somehow converted to a numerical value from a string? Or will the values be recognized as numerical without need for conversion? I have not gotten it to run that far to experiment yet.
I have attached the workbook as well.
Any help is greatly appreciated!
Thanks,
Chris
I'm trying to create a user form that allows the user to input latitude, longitude and a radius, and have the distance between that point and a set of other points calculated. The values will then populate a blank column in the workbook. I have the following code im trying out:
Private Sub CalcDistButton1_Click()
Dim ScanRad As Single
Dim SubLat As Single, SubLon As Single
Dim FilterCell As Range, DataRange As Range
Dim x As Range, y As Range, z As Range
Dim a As Range, b As Range, c As Range
Set DataRange = Intersect(Worksheets("Sheet1").Columns(7), Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible))
'Pull scan radius value from input box
ScanRad = UserForm1.TextBox3.Text
SubLat = UserForm1.TextBox1.Text
SubLon = UserForm1.TextBox2.Text
'Calculate offset distance for unhidden rows
For Each FilterCell In DataRange
x = FilterCell.Offset(, 1)
y = FilterCell.Offset(, 2)
z = FilterCell.Offset(, 4)
'Test Calclulation
'a = x*y
If FilterCell.Value <> "" Then
FilterCell.Offset(, 5) = Application.Acos(Cos(Application.Radians(90 - SubLat)) * Cos(Application.Radians(90 - x)) + Sin(Application.Radians(90 - SubLat)) * _
Sin(Application.Radians(90 - x)) * Cos(Application.Radians(SubLon - y)) * ((z) / 5280))
End If
Next FilterCell
End Sub
My problem is that I'm not sure how to incorporate the values of x,y & z in the for loop. Does this require multiple if statements? I have one other question as well - for passing values from the text boxes in the user form to the procedure, would these need to be somehow converted to a numerical value from a string? Or will the values be recognized as numerical without need for conversion? I have not gotten it to run that far to experiment yet.
I have attached the workbook as well.
Any help is greatly appreciated!
Thanks,
Chris