0

OK, I've got a straight-forward 2-d block of data in excel: row 1 and column 1 are labels, the rest are numbers. My task right now is to put the sum of each column in the first empty cell(row) underneath.

Whereas my practice dataset is of known dimensions, the actual datasets I'll be using this program on will have a variable number of rows and columns. To this end, I can't just say "=SUM(B2:B20)" because the last filled cell won't always be B20 (for example). The easiest way to total each column, I thought, would be a FOR..NEXT loop, but I just can't get VS to accept the summation formula. Here's what I've got so far:

  `With xlWsheet2 'check for last filled row and column of transposed data'
        If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow2 = .Cells.Find(What:="*",
                               After:=.Cells(1, 1),
                               LookAt:=Excel.XlLookAt.xlPart,
                               LookIn:=Excel.XlFindLookIn.xlFormulas,
                               SearchOrder:=Excel.XlSearchOrder.xlByRows,
                               SearchDirection:=Excel.XlSearchDirection.xlPrevious,
                               MatchCase:=False).Row
        Else : lRow2 = 1
        End If

        If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
            lCol2 = .Cells.Find(What:="*",
                               After:=.Range("A1"),
                               LookAt:=Excel.XlLookAt.xlPart,
                               LookIn:=Excel.XlFindLookIn.xlFormulas,
                               SearchOrder:=Excel.XlSearchOrder.xlByRows,
                               SearchDirection:=Excel.XlSearchDirection.xlPrevious,
                               MatchCase:=False).Column
        Else : lCol2 = 1
        End If


        lastcell2 = xlWsheet2.Cells(lRow2, lCol2) 'defines last row, column of transposed data'
        emptyRow1 = xlWsheet2.Rows(lRow2).Offset(1) 'defines the first empty row'


        'add in cell of SUM underneath each column'
        For i As Integer = 2 To lCol2
            colTop = xlWsheet2.Cells(2, i)
            colBot = xlWsheet2.Cells(lRow2, i)

            ELtotal = xlWsheet2.Range(emptyRow1, i)
            ELtotal = .Sum(.Range(colTop, colBot))
        Next i
    End With

` Now, the ELtotal statements used to be one long line, but I was trying to see what part VS had a problem with. It breaks at the first one, .Range(emptyRow1, i). Here's other iterations of that equation I've tried that weren't accepted:

.Range(emptyRow1, I).Formula = "=SUM(colTop, colBot)"
.Range(emptyRow1, I).Formula = "=SUM(.cells(2,i), (lRow2,i))"
.Range(emptyRow1, I).Formula = .sum(.range(colTop, colBot)
.Range(emptyRow1, I).Value = etc...

ad inifintum

PS- I'm pretty new to this, so I'm probably going about this whole process the wrong way...

Improve this question
3
  • 1
    Can it be assumed that any row that contains data (numbers) will be labeled (in row 1) and that any column that contains data will be labeled? Do all columns contain the same number of data cells, or do some columns contain more data cells than others? If the answer to the latter is no, then how necessary is it that the sum be in the 'First' empty cell as opposed to 'An' empty cell? Commented Sep 6, 2014 at 1:06
  • @Clif, Yes, all rows AND columns will be the same length (with the same amount of data). Those cells that don't have numbers will have zero's. Commented Sep 8, 2014 at 22:31
  • And also, all columns and rows WILL have a label (entire column A and row 1). I gave thought to putting this whole data set into an 3d array, but since the data gets spit out into excel, I figured I'd try this way first. Commented Sep 8, 2014 at 22:37

3 Answers 3

Reset to default
1

Based on what you told me about the row and column headings, I believe that this code will do what you want, namely put a single column sum in the first empty cell underneath.

Sub find()   
 Dim lastrow As Long, lastcol As Long, thiscol As Long

    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
    For thiscol = 2 To lastcol
        Cells(lastrow + 1, thiscol).Select
        ActiveCell.Value = WorksheetFunction.Sum(Range(Cells(1, ActiveCell.Column), ActiveCell))
    Next
    End Sub

Best of luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Thanks Clif, seems like this is a better way, however I've been chasing down errors all morning. Currently, VS has accepted the syntax but I'm getting the "object variable or With block variable not set" message on the third line there where 'lastrow = Cells(Rows.Count.....' I tried changing the variable type of lastrow to excel.Range, but then the 'lastrow + 1' expression in the loop isn't valid bc a (+) can't be used in a range, apparently. Grrr!
Sorry to hear that the code is not working. I tested it using Excel 2010 and a very simple data set where I put the letter 'a' in cells A1:A5. I then put the letters b,c,d in cells B1, C1 and D1 respectively, and filled in cells B2:D5 with numbers. One thing that comes to mind is that 'lastrow' being data type long would be limited to values < 2,147,483,648 is there a possibility that your data set would have more than that number of rows? Is it all on one sheet? I am seeing a lot of posts about this error message on SO and excelforum among others. Let me know if I can be of further help.
More than 2 million rows? Sheesh, I hope not! It's usually between 25 and 35 rows, about the same number of columns. I'll try a simple set-up similar to what you did and see what happens.
Looks like Rows and ActiveCell are flagged for not being declared. Didn't know I had to declare those...?
So code is not working for you even with the set up that I described? I am puzzled as to why this should be, as it works on this end. My best thought at this point would be to post a new question asking if someone else can help, perhaps there are some coders on here that have encountered a similar situation/error and have a solution. Again, sorry about the problems.
|
0

This formula will do the trick of summing two whole columns, A and B in this case:

= sum($A:$B)

If it is possible for the headers to be interpreted as numeric values that might contribute to the sum then the formula should be amended to be

= sum($A:$B) - sum($A$1:$B$1)
Improve this answer

3 Comments

I don't want to add the columns together, rather I want to add up each column.
@BG-: Well, that's a pretty trivial simplification of my posted answer.
So then I could put '= sum($A:$A)' to get a total for the column?
0

In order to export to excel with sum of all numeric columns from DataGridView, add a button to your form and add the following code in its click event:-

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
    '--- Export to Excel -------------------------------------------------
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer
    Dim datarange As Excel.Range

    Dim save_file As New SaveFileDialog
    'give its extension
    ''save_file.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*"
    'save_file.Filter = "xls files (*.xls)|*.xls"
    save_file.Filter = "xls files (*.xls)|*.xls|Excel 2007|*.xlsx"
    ''Select xls
    save_file.FilterIndex = 2
    save_file.FileName = "My_excel_report_"
    save_file.RestoreDirectory = True

    Try
        If save_file.ShowDialog() = DialogResult.OK Then
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")

            For x = 0 To DataGridViewSummary1.ColumnCount - 1
                xlWorkSheet.Cells(0 + 1, x + 1) = _
                DataGridViewSummary1.Columns(x).HeaderText
            Next

            For i = 0 To DataGridViewSummary1.RowCount - 1
                For j = 0 To DataGridViewSummary1.ColumnCount - 1
                    If IsDate(DataGridViewSummary1(j, i).Value) Then
                        'MsgBox("The cell value is date")
                        xlWorkSheet.Cells(i + 2, j + 1) = FormatDateTime(CDate(DataGridViewSummary1(j, i).Value.ToString), DateFormat.ShortDate)
                        xlWorkSheet.Cells(i + 2, j + 1).HorizontalAlignment = Excel.Constants.xlCenter
                        xlWorkSheet.Cells(i + 2, j + 1).VerticalAlignment = Excel.Constants.xlCenter
                    Else
                        xlWorkSheet.Cells(i + 2, j + 1) = _
                            DataGridViewSummary1(j, i).Value.ToString()
                    End If
                Next
            Next

            datarange = xlWorkBook.ActiveSheet.UsedRange
            datarange.Font.Name = "Consolas"
            datarange.Font.Size = 10
            '--- added on 07/09/2016 -------------------------------------------------------------------
            Dim lastrow, lastcol As Long
            With xlWorkSheet
                lastcol = .Cells(1, .Columns.Count).End(Excel.XlDirection.xlToLeft).Column
                lastrow = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row
            End With
            'MessageBox.Show("The last column in Sheet1 which has data is " & lastcol)
            'MessageBox.Show("The last row in Col A of Sheet1 which has data is " & lastrow)

            For i = 2 To lastcol
                If IsNumeric(xlWorkSheet.Cells(lastrow, i).Value) Then
                    xlWorkSheet.Cells(lastrow + 1, i).Select()
                    xlWorkSheet.Cells(lastrow + 1, i).Value = xlApp.WorksheetFunction.Sum(xlWorkSheet.Range(xlWorkSheet.Cells(1, i), xlWorkSheet.Cells(lastrow + 1, i)))
                End If
            Next i
            xlWorkSheet.Columns.AutoFit()
            '----------------------------------------------------------------------------------------------
            xlWorkSheet.SaveAs(save_file.FileName)  'sd.filename reurns save file dialog path
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
             '--------------------------------------

            Dim proc As Process = Nothing
            Dim startInfo As New ProcessStartInfo
            startInfo.FileName = "EXCEL.EXE"
            startInfo.Arguments = save_file.FileName
            Process.Start(startInfo)
        End If
            Catch ex As Exception
        MessageBox.Show(ex.ToString)
        'GlobalErrorHandler(ex)
    End Try
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.