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"