How to sum variable columns and rows using vba

[Magnolia1]

Hello Everyone.
I am reaching out to all of you for help... I have a table that has few rows and few columns. I would like to total all columns just like listed below. The issue is that rows and columns will be changed dynamically from month to month therefore the number of columns and rows will increase every month.
1/1/16 2/1/16 3/1/16
apple 1 5 3
pear 2 3 2
plum 1 3 4
Totals: 4 11 9

here is my vba code:
Dim LastColumn As String
Dim LastRow As String
Dim FirstColumn As String
Dim FirstRow As String
Dim r As Long
Dim v As Long
Dim c As Long

LastRow = xlWs2.Range("B2", xlWs2.Range("B2").End(xlDown)).Rows.Count +1
LastColumn = xlWs2.Range("B2", xlWs2.Range("B2").End(xlToRight)).Columns.Count + 1
xlWs2.Cells(LastRow, 2).Value = "Totals"



For c = FirstColumn To LastColumn
xlWs2.Cells(LastRow, c).Font.Bold = True
Next

For v = 2 To LastColumn
For r = 2 To LastRow

xlWs2.Cells(LastRow).Value = "=SUM(Cells(2, 2) & LastColumn + 1 & )"
Next r
Next v
The code gives me an error #1004 when it gets to sum function and I am having a hard time to find the correct syntax. can someone please help me with it?

Thank you.

[ssanfu]

I am very confused .... from the code it looks like you are writing to an Excel spreadsheet even though you talk about a table.

From the code, it looks like there is a lot more code that is not posted.

However, given your data, I set up a spreadsheet and wrote some EXCEL code to add a formula row.

Sub YourSubName()
    Const StartDataRow As Integer = 3  'first row for sum function is always this number

    Dim EndDataRow As Integer
    Dim LastDataColumn As Integer
    Dim FormulaRow As Integer
    Dim k As Integer   'kounter
    Dim SumRow As String
    Dim SumCol As String

    'last column
    Range("B2").Select
    Selection.End(xlToRight).Select
    LastDataColumn = ActiveCell.Cells.Column

    'last data row
    Range("B2").Select
    Selection.End(xlDown).Select
    EndDataRow = ActiveCell.Cells.Row

    '  formula row number
    FormulaRow = EndDataRow + 1

    ' loop through columns
    For k = 2 To LastDataColumn   'first column will be "B", then increment as k increments
        SumRow = Chr(64 + k) & StartDataRow
        SumCol = Chr(64 + k) & EndDataRow

        ActiveSheet.Cells(FormulaRow, k).Formula = "=SUM(" & SumRow & ":" & SumCol & ")"
    Next
End Sub

Your task is to adapt this code to work in your dB.....

(its been a long time (years) since I've done any coding in Excel! Whew!)

Good luck with your project...

[Magnolia1]

Hello Steve, Thank you for your response. I am sorry for the confusion. I imported the data from access into excel and then I format this data by using Access vba because I reused variables that I used for exporting data. You are absolutely correct that the example that I posted with my question is only a small portion of my complete code. I did not post the whole thing as it is very big and it can confuse people.
I tried to adapt your code, but unfortunately it gives me 0s in total row instead of calculated amounts.

Const StartDataRow As Integer = 3
Dim EndDataRow As Integer
Dim LastDataColumn As Integer
Dim FormulaRow As Integer
Dim k As Integer
Dim SumRow As String
Dim SumCol As String


Cells(4, 3).Select
Selection.End(xlToRight).Select
LastColumn = ActiveCell.Cells.Column


Cells(4, 3).Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Cells.Row


FormulaRow = EndDataRow + 2

For k = 2 To LastDataColumn 'first column will be "B", then increment as k increments
SumRow = Chr(64 + k) & StartDataRow
SumCol = Chr(64 + k) & EndDataRow
ActiveSheet.Cells(FormulaRow, k).Formula = "=SUM(" & SumRow & ":" & SumCol & ")"
Next

Any input is appreciated.

[ssanfu]

OK, well here is the thing.

In your example in post#1, your code indicates that you are using Access automation to control Excel.
You didn't post all of the automation code so I don't know the declarations. I can see you have some code that begins "xlWs2.".

The code I posted is for Excel. You are going to have to modify the Excel code to use the Access automation code you have. You can't change variables in code without modifying declarations also. And you have to change all occurrences of the variables.
(you changed "LastDataColumn" to "LastColumn" and "EndDataRow" to "LastRow"... no wonder there were errors)

You posted code with "B2", but you are now using "Cells(4, 3).Select" ("C4") ???

You have to modify/merge the Excel code into your procedure. The code works - see the attachment - but the straight Excel code will not work in your procedure.
Lines will have to begin with something like "xlWs2."

[Magnolia1]

Thank you Steve for your response. I actually combined part of your code with what I came up and the final version of the code works great:

Dim LastColumn As String
Dim LastRow As String
Dim FirstColumn As String
Dim FirstRow As String
Dim k As Integer
Dim StartDataColumn As String
Dim StartDataRow As String
StartDataColumn = 3
StartDataRow = 4

'finding last row and last column
LastRow = xlWs2.Range("C4", xlWs2.Range("C4").End(xlDown)).Rows.Count + 3
LastColumn = xlWs2.Range("C4", xlWs2.Range("C4").End(xlToRight)).Columns.Count + 1
'summing rows
For k = StartDataColumn To LastColumn
xlWs2.Cells(LastRow, k).Formula = "=SUM(" & ConvertToLetter(k) & StartDataRow & ":" & ConvertToLetter(k) & LastRow - 1 & ")"
Thanks again. have a good day.

[ssanfu]

Glad you got it sorted out....