Q:

Double for loop in Excel VBA

I wrote a code to calculate a variable SBHP using single values of variables SWHP and SWHT. Now I want to extend the code so that it can calculate SBHP for a list of SWHP and SWHT but don't know how can I achieve that. The code for single value calculation is give below. Example of multiple values of SWHP and SWHT respectively are 2500, 2600, 2700 and 35, 36, 37 for which SBHP needs to be calculated.
I have tried nested for loop, one for SWHT and other for SWHT but failed miserably and I am new to VBA coding.
Sub StaticBHPCalculation()

Dim SWHP As Double
Dim WellLength As Double
Dim SBHP(1 To 50) As Variant
Dim SBHP_initial As Single
Dim SWHT As Double
Dim Tres As Double
Dim PAverage As Double
Dim TAverage As Double
Dim Zi As Single
Dim s As Single
Dim GasSG As Single
Dim WellIncl As Single
Dim Counter As Integer

Zi = 0.77
SWHP = Range("C6").Value
SWHT = Range("C6").Offset(-1, 0).Value
Tres = Range("C6").Offset(1, 0).Value
WellLength = Range("C6").Offset(2, 0).Value
GasSG = Range("C6").Offset(-4, 0).Value
WellIncl = Range("C6").Offset(3, 0).Value

SBHP_initial = SWHP + 0.25 * (SWHP / 100) * (WellLength / 100)

    For Counter = 1 To 50
        SBHP(Counter) = SBHP_initial
        PAverage = (SWHP + SBHP(Counter)) / 2
        TAverage = (SWHT + Tres) / 2 + 460
        s = (0.0375 * GasSG * WellLength * Cos(WellIncl)) / (Zi * TAverage)
        SBHP(Counter + 1) = SWHP * Exp(s / 2)
            If Abs(SBHP(Counter + 1) - SBHP(Counter)) <= 0.01 Then
        Exit For

        Else: SBHP_initial = SBHP(Counter + 1)
            End If
    Next Counter
Range("C6").Offset(4, 0).Value = SBHP(Counter)
End Sub

A:

this is the final code.
Sub StaticBHPCalculation()

Dim WellLength As Double
Dim SBHP(1 To 10) As Variant
Dim SBHP_initial As Single
Dim Tres As Double
Dim PAverage As Double
Dim TAverage As Double
Dim Zi As Single
Dim s As Single
Dim GasSG As Single
Dim WellIncl As Single
Dim Counter As Integer
Dim SWHP() As Long
Dim SWHT() As Long
Dim SWHP_Tcounter As Integer
Dim Dimension1 As Integer

Dimension1 = Range("B11", Range("B10").End(xlDown)).Cells.Count
ReDim SWHP(1 To Dimension1)
ReDim SWHT(1 To Dimension1)

GasSG = Range("C2").Value
Tres = Range("C2").Offset(1, 0).Value
WellLength = Range("C2").Offset(4, 0).Value
WellIncl = Range("C2").Offset(5, 0).Value

For SWHP_Tcounter = LBound(SWHP) To UBound(SWHP)
    SWHP(SWHP_Tcounter) = Range("B10").Offset(SWHP_Tcounter, 0).Value
    SWHT(SWHP_Tcounter) = Range("C10").Offset(SWHP_Tcounter, 0).Value

    SBHP_initial = SWHP(SWHP_Tcounter) + 0.25 * (SWHP(SWHP_Tcounter) / 100) * (WellLength / 100)
    For Counter = 1 To 50
        SBHP(Counter) = SBHP_initial
        PAverage = (SWHP(SWHP_Tcounter) + SBHP(Counter)) / 2
        TAverage = (SWHT(SWHP_Tcounter) + Tres) / 2 + 460
        Zi = Zfactor(TAverage, PAverage)
            s = (0.0375 * GasSG * WellLength * Cos(WellIncl)) / (Zi * TAverage)
            SBHP(Counter + 1) = SWHP(SWHP_Tcounter) * Exp(s / 2)
                If Abs(SBHP(Counter + 1) - SBHP(Counter)) <= 0.01 Then
                Exit For

                Else: SBHP_initial = SBHP(Counter + 1)
                End If
    Next Counter
    Range("D10").Offset(SWHP_Tcounter, 0).Value = SBHP(Counter)
Next SWHP_Tcounter
Erase SBHP
Erase SWHP
Erase SWHT
End Sub

