Need help passing variable values to For Loop

[cwb1021]

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

[mdmackillop]

Nothing wrong with your incorporation of variables but I got an error value from your formula. I've split it into constituent parts for ease of checking and "reassembled", but I may have missed some bracketing. I' m sure you can fix that. Also I've changed your DataRange to exclude the header; you'll need to fix this if using a filter.


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 Variant, y As Variant, z As Variant
Dim a, b, c, d, e, f


'Set DataRange = Intersect(Worksheets("Sheet1").Columns(9), Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeVisible))
With Worksheets("Sheet1")
Set DataRange = Range(.Cells(2, 9), .Cells(Rows.Count, 9).End(xlUp))
End With


'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)

'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))
With Application
a = .Acos(Cos(.Radians(90 - SubLat)))
b = Cos(.Radians(90 - x))
c = Sin(.Radians(90 - SubLat))
d = Sin(.Radians(90 - x))
e = Cos(.Radians(SubLon - y))
f = (z) / 5280
FilterCell.Offset(, 5) = a * b + c * d * e * f
End With
Next FilterCell
End Sub

[snb]

This should be sufficient:

Private Sub CalcDistButton1_Click()
sn=sheets("sheet1").columns(9).specialcells(2).resize(,6)

with application
for j=2 to ubound(sn)
sn(j,6)=.Acos(Cos(.Radians(90 - Textbox1.text))) *Cos(.Radians(90 - sn(j,2))+ Sin(.Radians(90 - textbox1.text))* Sin(.Radians(90 - sn(j,2))) * Cos(.Radians(textbox2.Text - sn(j,3))) * sn(j,5) / 5280
next
end with

sheets("sheet1").columns(9).specialcells(2).resize(,6)=sn
End Sub

[offthelip]

I have two comments about your code, firstly you appear to be using a flat earth model for calculating the distance between two points, this is Ok provided the two point are reasonably close together. i:e with a few miles. Secondly you have defined your lat long variables as "single" . In the equations you effectivly subtract one latitude from another, if the two points are very close together your result will have very few significant figures. this gets worst the closer they are.
I suggest you declare the lat longs as "Double"