CODEWITHSUNDEEP
excelvba
DanielTee1
DanielTee1

Reputation: 1

VBA code, summing values in a single cell, of a row, and add with another cell with range of numbers in it

Cell: S1 = 0.50   Cell T1 = 1.00   Cell U1 = 0.02
           0.50             0.05             0.63
           0.50             0.25             0.89

Ran a formula out to the right to find the "." symbol and the extract the data using the Mid Formula. But some cells can have 12 vaules in them, and some with only 1. I would like the formula to find the total number of Values in the cell, and total the amount.

I have no code

Results should sum the total amount in the cell, and sum together the amounts with the other column and create a 4th column with the total.

Upvotes: 0

Views: 645

Answers (3)

Scott Craner
Scott Craner

Reputation: 152505

No need for vba, this formula will do it:

=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW($A$1:INDEX($A:$A,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+1,99)))

enter image description here

Upvotes: 0

Michal
Michal

Reputation: 5857

Consider using non-VBA solution by using EVALUATE function:

enter image description here enter image description here

Upvotes: 0

chillin
chillin

Reputation: 4486

Untested. If I have understood correctly, the cells are delimited by new line character.

So maybe something like:

Option explicit

Private Sub SplitAndTotal()

' Change name of sheet as necessary
With thisworkbook.worksheets("Sheet1")
.range("V1").value2 = SplitIntoNumbersAndSum(.range("S1").value2) + _
SplitIntoNumbersAndSum(.range("T1").value2) + _
SplitIntoNumbersAndSum(.range("U1").value2)
End with

End sub

Private Function SplitIntoNumbersAndSum(byval valueToSplit as variant, optional byval delimiter as string = vbnewline) as double

Dim Total as double

Dim arrayOfValues as variant
arrayOfValues = split(valueToSplit, delimiter, -1, vbbinarycompare)

Dim index as long
For index = lbound(arrayOfValues) to ubound (arrayOfValues)
If isnumeric(arrayOfValues(index)) then
Total = total + cdbl(arrayOfValues(index))
End if

Next index

SplitIntoNumbersAndSum = Total

End function

Sorry for bad formatting and indentation, not on a desktop.

If the function constantly returns a zero, try checking/changing the delimiter.

You might also want to try calling the function as a UDF directly from the worksheet itself (though you may need to put the function's code in a separate module), as opposed to calling it through a dedicated macro/procedure.

Upvotes: 0

Related Questions