looping problem in vba 1

[ido233]

Hi I have this code that i got from someone and i have been trying to get rid of this goto statment and i cant seem to change the code to work.here is the code.

Public Sub sunfac(elev1, ill, irr)

Dim sunf As Double ' calculated illum in foot-candles
Dim xi, x1, x2, y1, y2 As Double ' intermediate variables for interpolation
Dim j As Integer

x = Array(-90, -70, -55, -41, -35, -33, -31, -29, -27, -26, -25, -24, -23, -22, -21, -20, -19, -18, -17, -16.5, -16, -15.5, -15, -14.5, -14, -13.5, -13, -12.5, -12, -11.5, -11, -10.5, -10, -9.5, -9, -8.5, -8, -7.5, -7, -6, -5, -4.5, -4, -3.5, -3, -2.5, -2, -1.5, -1, 0, 1, 2, 4, 6, 9, 12, 15, 20, 25, 32, 40, 53, 60, 70, 80, 90)
y = Array(0.000028, 0.0000281, 0.0000288, 0.0000302, 0.0000314, 0.000032, 0.0000327, 0.0000336, 0.0000348, 0.0000356, 0.0000366, 0.0000378, 0.0000394, 0.0000414, 0.0000441, 0.0000477, 0.0000528, 0.0000605, 0.000073, 0.0000823, 0.0000957, 0.000114, 0.00014, 0.000175, 0.000224, 0.000292, 0.000392, 0.00054, 0.000772, 0.00114, 0.00174, 0.00268, 0.0043, 0.00697, 0.0116, 0.0193, 0.0325, 0.056, 0.1, 0.316, 1, 1.72, 2.92, 4.84, 7.8, 12.1, 18, 26.2, 37, 68, 116, 183, 350, 543, 883, 1290, 1725, 2540, 3410, 4750, 6200, 8405, 9420, 10510, 11225, 11500)

j = 2

' Start of loop
10: 'looping point

If (elev1 < x(j)) Then
If (elev1 > -20 And elev1 < 1) Then
sunf = rinter(elev1, x(j - 1), x(j), y(j - 1), y(j))
Else
sunf = Exp(rinter(elev1, x(j - 1), x(j), Log(y(j - 1)), Log(y(j))))
End If
End If

If (elev1 > x(j)) Then
j = j + 1
If (j < 66) Then
GoTo 10
Else
j = 66
sunf = y(j)
End If
End If

ill = sunf * 10760

If (ill < 0.85) Then ill = 0.85

irr = ill * 0.42042

End Sub

thanks

 

[TonyJollans]

Just from glancing at it (so I may have made a silly mistake) ...

:
:
j = 1

Do 
j = j + 1

If (elev1 < x(j)) Then
 If (elev1 > -20 And elev1 < 1) Then
    sunf = rinter(elev1, x(j - 1), x(j), y(j - 1), y(j))
 Else
    sunf = Exp(rinter(elev1, x(j - 1), x(j), Log(y(j - 1)), Log(y(j))))
 End If
End If

Loop while j < 65 and (elev1 > x(j)) 

If (elev1 > x(j)) Then
  sunf = y(j)
 End If

ill = etc.
:
:

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Bong]

maybe this part:

' Start of loop
10: 'looping point

If (elev1 < x(j)) Then
If (elev1 > -20 And elev1 < 1) Then
sunf = rinter(elev1, x(j - 1), x(j), y(j - 1), y(j))
Else
sunf = Exp(rinter(elev1, x(j - 1), x(j), Log(y(j - 1)), Log(y(j))))
End If
End If

If (elev1 > x(j)) Then
j = j + 1
If (j < 66) Then
GoTo 10
Else
j = 66
sunf = y(j)
End If
End If

should be:

' Start of loop
10: 'looping point
[red]for j=2 to 66[/red]
If (elev1 < x(j)) Then
 If (elev1 > -20 And elev1 < 1) Then
    sunf = rinter(elev1, x(j - 1), x(j), y(j - 1), y(j))
 Else
    sunf = Exp(rinter(elev1, x(j - 1), x(j), Log(y(j - 1)), Log(y(j))))
 End If
End If

If (elev1 [red]<[/red] x(j)) Then [red]break[/red]
[red]next
if j=66 then sunf=y(j)[/red]

_________________
Bob Rashkin