Using Sum with range cell-variable referencing

Howdy ya'll,

So, I have created a macro that upon importing a script (I work at a polling firm) as text file formats the file in a particular way. I thought I had finished this project, but my colleague informed that it isn't helpful when a call goes on for multiple days.

I believe there are three major tasks with the next step.

First, create a total cell. In other words, when there is a row with "Total" in it, I need to Sum those cells from D to L and place the value in M column.
Second, total all the responses (these vary) across the different days.
Third, using the totals create column segments that represent that data as a percentage.

I have been fiddling around with my totals row, and found a few examples but it doesn't seem to work for me. The code I have been trying is in bolded red.



The code is below.

Sub ToplineMultiDay()

Application.ScreenUpdating = False

Dim wrksheet As Worksheet
Dim col As Byte
Dim row As Byte
Dim i As Byte
Dim SampleSize As String
Dim lrow As Byte
Dim lcol As Byte
Dim EndRow As Byte
Dim TotalRowEnd As Long


ActiveSheet.Name = "Sheet1"

'Inserting extra rows

Rows("1:8").Insert Shift:=xlDown

'Changing fonts

Range("A1").Value = "Advantage, Inc."

Range("A1").Select
With selection.Font
    .Name = "Cambria"
    .Bold = True
    .Size = 14
    .Color = RGB(192, 0, 0)
    .Underline = True
End With


Range("A2") = InputBox("What is the Client's Name?")
Range("A2").Select
With selection.Font
    .Name = "Cambria"
    .Bold = True
    .Size = 12
End With

Columns("A").HorizontalAlignment = xlLeft
Columns("A").ColumnWidth = 19.43
'Columns("C").ColumnWidth = 5.29

'Creating date and commentary

Range("A3").Value = "=TODAY() - 1"
Range("A3").Select
With selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
End With
   
'Adding more segments

Range("A5").Value = "Start Date"
Range("A6").Value = "End Date"


Range("M9").Value = "Total"
Range("M9").Select
With selection.Font
    .Size = 12
    .Bold = True
    .Name = "Cambria"
End With

Range("N9").Value = "Total Percentage"
Range("N9").Select
    With selection.Font
        .Size = 12
        .Bold = True
        .Name = "Cambria"
    End With
    
Range("N:N").NumberFormat = "0.00%"
Range("E:L").EntireColumn.Hidden = True



'Finding last row

lrow = Cells(Rows.Count, "A").End(xlUp).row
Debug.Print lrow
EndRow = lrow + 2
Debug.Print EndRow
TotalRowEnd = lrow + 74
Debug.Print TotalRowEnd

'Bolding/Change Font

w = 8

Do Until w = EndRow
    If Cells(w, 1) = "" Then
        Cells(w + 1, 2).Select
        With selection.Font
            .Bold = True
            .Name = "Cambria"
        End With
        Cells(w + 1, 3).Select
        With selection.Font
            .Bold = True
            .Name = "Cambria"
        End With
        Cells(w + 1, 1).Select
        With selection.Font
            .Bold = True
            .Name = "Cambria"
        End With
    End If
     
w = w + 1

Loop


'Inserting Total rows and adding totals

x = 9

Do Until x = EndRow
    If Cells(x, 1) = "" Then
        Cells(x, 2).Value = "Total"
        Cells(x, 2).Select
        With selection.Font
            .Bold = True
            .Name = "Cambria"
        End With
        Rows(x).Select
        With selection.Font
            .Bold = True
            .Name = "Cambria"
        End With
    End If
    If Cells(x, 2) = "Total" Then
        Range(Cells(x, 13)).Value = "=Sum((" & RangeCells(x, 4), Cells(x, 5), Cells(x, 6)) & ")"
    End If
x = x + 1
    
Loop

'Adding a row between Total and next column

y = 9

Do Until y = TotalRowEnd
    If Cells(y, 2) = "Total" Then
        Rows(y).Offset(1).EntireRow.Insert
    End If
y = y + 1

Loop

End Sub

The first way gives me a wrong number of arguments or invalid property error.

This is a second way I tried:

Sheets("Sheet1").Range.Cells(x, 13).Value = Application.Sum(Cells(x, 4), Cells(x, 5), Cells(x, 6), Cells(x, 7), Cells(x, 8), Cells(x, 9), Cells(x, 10), Cells(x, 11), Cells(x, 12))

Gives me essentially a range global failure error.

I also tried this, based on a stack overflow forum post.

Range(Cells(x,13).Value = "=Sum(" & Range(Cells(x,4), Cells(x,5), Cells(x,6), Cells(x,7)).Address(False, False) & ")"

This way just freaks out.

I have just now tried:

Cells(x,13).Forumla = "=Sum(Range(Cells(x,4), Cells(x,5), Cells(x,6))"

This doesn't give me a debug error message, but it does give me a formula error message of #NAME?. So, I know I am onto something. I just can't figure out the right combination of range(cell(row,col index) commentary to get it to work.

I have been up and down the forums and also in my Walkenbach Excel 2010 book, but cannot seem to find anything that actually works.


Help finding the correct formulation would be fabulous. Also, if anyone has best practices for how to sum the other rows with varied number of responses and lack of key words that would be awesome too.

Thanks in advance for any and all insight!

Rachelle