Sum function in excel with addons.?

Sum function in excel with addons.?
At work they use a barcode scanner to input information into Excel. I would like to sum all of the numbers in a certain column into a cell. This is usually easy, right? =Sum(E4:E100), or whatever. My problem is, this excel document has some addins, and code in these “E” cells that grabs its value from the barcode. I can only sum cells that have a barcode scanned in it, because then it shows a number, otherwise its a blank cell, and (my guess is) the code in it messes up the sum function because it shows #VALUE if I try to sum cells passed the ones with a number in them. But this kinda defeats the purpose… Is there a way to make it sum E:4 through last E cell with a number?

Best answer:

Try this event handler. It will sum all numeric values between E4 and the last row in column E containing data. It will ignore non-numeric entries. If you do not wish the sum to appear in F1, change the “F1″ reference to the cell you wish to display the total in.

Copy the code to the clipboard:

Private Sub Worksheet_selectionChange(ByVal Target As Range)
Dim LastRow
LastRow = Range(“E” & Rows.Count).End(xlUp).Row
Range(“F1″).Value = Application.Sum(Range(“E4:E” & LastRow))
End Sub

Select the appropriate worksheet and right click on the sheet tab.

Select ‘View Code’.

Paste the code into the sheet module editing area to the right.

Close the VBE and return to Excel.

Enter data.

Tags: addons., Excel, function

Under Forum