snb and Rick,

Just tested snb's latest submission.
Both coding submissions work nicely. Both take about the same amount of time to run and both are clearly brilliant examples that I certainly could not get from anyone else.
snb, thank you for providing an integrated view of your code, very helpful and well done.

Thank you Europe and the United States

Steve



Quote Originally Posted by snb View Post
@Rick
Although the OP did't ask for any flexibility, I think it's the most flexible one:

Code:
Sub M_snb()
  Range("C9:F10").Name = "snb1"
  Range("I9:L10").Name = "snb2"
  
  [snb1] = [if(snb1*snb2>0,int((snb1+snb2)/2),if(snb1&snb2="","",snb1+snb2))]
End Sub
If you 'abhorr' square brackets, use

Code:
Range("snb1") = Evaluate("if(snb1*snb2>0,int((snb1+snb2)/2),if(snb1&snb2="""","""",snb1+snb2))")
@Steve

Avoid activate in VBA.
The use of 'Let' is redundant in VBA.
I tried to incorporate my suggestion into your snippet:
Code:
Sub Freeze()
    Dim Tt As Long
    Dim Rb As Integer
    Tt = Application.Range("WHERE15").Value
    Rb = Application.Range("reachBACK").Offset(0, -1).Value
    
    If Rb > Tt - 3 Then Rb = Tt - 4
    If Rb <> 0 Then
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        With Sheets("INVESTOR INTERFACE")
            .Cells(Tt - Rb, 128).Resize(Rb, 3).Name = "snb1"
            .Range("snb1").Offset(, 13).Name = "snb2"
            [snb1] = [if(snb1*snb2>0,int((snb1+snb2)/2),if(snb1&snb2="","",snb1+snb2))]
            If Tt > 12 Then .Range("snb1").Offset(-11, 0).Resize(24, 3).ClearContents
        End With
        Application.Calculation = xlCalculationAutomatic
    End If
End Sub