WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Auto Sum Two Columns In Excel

 Keywords: Auto Sum Two Columns Excel Formula =SUM Last Row Data Range Previous XLS XLSX XLSM 


;***************************************************************************
;**
;**       Auto Sum Two Columns In Excel
;**
;**  WinBatch 2012B
;**  Excel 2010
;**  Deana Falk 2012.09.26:
;***************************************************************************


filename = 'C:\TEMP\Sum.xlsm'
HeaderFlag = @TRUE     ; @True XLS has a First Row Header; @False No header.
OutputRow = "C"        ; This will SUM Columns A and B and place the results into Column C

oXL = ObjectCreate("Excel.Application")
oXL.Visible = @TRUE    ; Change to @False is your want this to happen behind the scenes.

; Define Constants
xlValues = -4163
xlPart = 2
xlByRows = 1
xlPrevious = 2

oXL.WorkBooks.Open(filename)
oSheet = oXL.ActiveWorkbook.Worksheets(1)

; Get Last Row of Data from Column A
objFind  =  oSheet.Cells.Find(::What="*", After=oSheet.Range("A1"), LookIn=xlValues, LookAt=xlPart, SearchOrder=xlByRows, SearchDirection=xlPrevious)
If objFind == 0 Then LastRow = 0 ;No Last Row
Else LastRow = objFind.Row

; Check HeaderFlag to determine if file has a header row that should be ignored.
If HeaderFlag Then myRange = OutputRow:"2:":OutputRow:LastRow
Else myRange = OutputRow:"1:":OutputRow:LastRow

; Select the Range where you want to insert the output
oSelection=oXL.Range(myRange).Select

; Insert Formula that Sums the two previous rows
oXL.Selection.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"

; Close the Workbork
oXL.WorkBooks.Close()

; Cleanup
oSelection = 0
oSheet = 0
oxL.Quit
oXL = 0

Article ID:   W18115
Filename:   Auto Sum Two Columns In Excel.txt
File Created: 2017:07:28:13:01:32
Last Updated: 2012:09:26:13:21:49