Dropping variable in Year n if selected in years prior to n

[totalnatal]

Hello,

I have the following code:

Sub X()

Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, sa, t, u, v, w, x, y, z, aa, ab, ac, ad As Long

s = 0

Do

For n = 1 To Range("f1").Value
    
    a = Range("i3").Offset(s, 1).Value
    b = Range("i3").Offset(s, 2).Value
    c = Range("i3").Offset(s, 3).Value
    d = Range("i3").Offset(s, 4).Value
    e = Range("i3").Offset(s, 5).Value
    f = Range("i3").Offset(s, 6).Value
    g = Range("i3").Offset(s, 7).Value
    h = Range("i3").Offset(s, 8).Value
    i = Range("i3").Offset(s, 9).Value
    j = Range("i3").Offset(s, 10).Value
    
    k = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:J"), 2)
    l = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:K"), 3)
    m = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:L"), 4)
    n = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:M"), 5)
    o = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:N"), 6)
    p = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:O"), 7)
    q = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:P"), 8)
    r = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:Q"), 9)
    sa = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:R"), 10)
    t = Worksheet.Function.VLookup(Worksheet.Function.Edate(Range("i3").Offset(s, 0).Value, n * 12), Range("I:S"), 11)
   
    u = k / a - 1
    v = l / b - 1
    w = m / c - 1
    x = n / d - 1
    y = o / e - 1
    z = p / f - 1
    aa = q / g - 1
    ab = r / h - 1
    ac = sa / i - 1
    ad = t / j - 1
    
  
    Range("t3").Offset(s, n).Value = Worksheet.Function.Max(u, v, w, x, y, z, aa, ab, ac, ad)

Basically I want the maximum out of the above variables from u to ad. However, if in n = 1 say u was the max, then for subsequent n(2,3...) never include that variable in the Max function again.

How would you go about doing this?

Thank you

 

[Ralajer]

The bottom your code didn't post so I wasn't sure what the Do loop was doing. I don't know what your data is like so I haven't been able to test it but the following code should work if I understand your requirements.

Sub x()

    Dim val As Variant
    Dim lookUpReturn As Variant
    Dim lookUpVal As Variant
    Dim lookUpRng As Range
    Dim result As Variant
    
    Dim WasMax() As Boolean
    Dim maxVal As Variant
    Dim maxPos As Integer
    
    Dim i As Integer, n As Integer, s As Integer
    
    Dim baseRng As Range
    
    Set baseRng = Range("I3")
    
    s = 0
    
    ' Do '// Bottom half was cutoff
    
    ReDim WasMax(1 To 10)
    
    For n = 1 To Range("F1").Value
    
    maxVal = -1000000000000#        '// Exteremely negative too check for max
        
        For i = 1 To 10
            '// If the position has result in max skip loop
            If WasMax(i) Then GoTo skipLoop
        
            val = baseRng.Offset(s, i).Value
            '// Set Range to From Column I to Column I + i
            Set lookUpRng = Range(Columns(9), Columns(9 + i))
    
            With Worksheet.Function
                lookUpVal = .EDate(baseRng.Offset(s, 0).Value, n * 12)
                lookUpCol = i + 1
                lookUpReturn = .VLookup(lookUpVal, lookUpRng, lookUpCol)
            End With
            
            result = (lookUpReturn / val) - 1
            
            If result > maxVal Then
                maxVal = result
                maxPos = i
            End If
            
skipLoop:
        Next i
        '// Mark position that has been a max before
        If n = 1 Then WasMax(maxPos) = True
        
        Range("T3").Offset(s, n).Value = maxVal
        
    Next n
    '// Loop While
    
End Sub

I wasn't sure if the max position was to be excluded only for n=1 or if for n=2..n as well so by the time you reach n there is only one value possible. I went with the former.