For Each Loop in Google Sheets? VBA For Example

Question

In Picture 1, the green cell means a column with a formula. The only columns I want to sum in row 13 are the the columns that don't have formulas in them, and columns that = "Upfront Costs" and merged with row 10. So in Picture 1, the answer would be $97(the sum of (A13:F13) - D13).

Picture 1

VBA for Picture 1 Example

Sub test()
 Dim cl As Object, count As Double
    For Each cl In Sheets("Sheet1").Range("10:10")
        If cl.MergeCells Then
            If cl.Offset(3, 0).HasFormula = False And cl.MergeArea.Cells(1, 1).Value = "Upfront Costs" Then
                count = count + cl.Offset(3, 0).Value
            End If
        Else
            MsgBox count
            Exit Sub
        End If
    Next cl
End Sub

In Picture 2, If "Upfront Costs" was merged from "B10:L10" the answer would be $289 (the sum of (B13:L13) - D13).

Picture 2

In Picture 3 example, if "Upfront Costs" was merged from "L10:N10" the answer would be $122 (sum of L13:N13")

Picture 3

Answer 1

You can do like this:

function fnct_sum() 
{
    var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
    var cl , count=0 ;
    var yourRange = sheet.getRange("10:10");
    for (var i = 1; i < yourRange.getNumColumns()+1; i++)
    {
        cl=yourRange.getCell(1, i);
        if (cl.isPartOfMerge())
        {
            Logger.log(cl.getMergedRanges()[0].getCell(1, 1).getValue());
            if (cl.offset(3, 0).getFormula()=="" && cl.getMergedRanges()[0].getCell(1, 1).getValue()=='Upfront Costs') 
            {
               count = count + cl.offset(3, 0).getValue();
            }

            else 
            {
               Logger.log (count);
               //break;
            }   
       }
    }
    Logger.log (count);
};