how to sum a multiline cell?

[Jaspertje]

Hey guys,

Since i made a qoutation system for my boss i'm working with excel 'nd stuff, and it is going pretty well.
I'm able to add products to a datagridview and export this dgv to excel.

In this picture you see my excel file which has the exported products in it


As you can see, in cell C17, the text is based on the ALT+Enter function.

The code which exports the products is the following:

Some explaination with the codes:
I wanted to make a qoutation which has 5 categories with it. "Riolering" is one of the categories. Thats why i used the if-loop.
Little translation to understand:
artikelnaam = productname
hoeveelheid = quantity
eenheid = unit
prijs = price

in this script i translated a little of the dutch words, to make it easier to understand.

For cnt = 0 To (artikelengrid.Rows.Count - 1)
                        Dim productname As String = artikelengrid.Rows(cnt).Cells(0).Value.ToString()
            Dim category As String = artikelengrid.Rows(cnt).Cells(1).Value.ToString()
            Dim ehd As String = artikelengrid.Rows(cnt).Cells(2).Value.ToString()
            Dim qty As String = artikelengrid.Rows(cnt).Cells(3).Value.ToString()

.....

  ElseIf category = "Riolering" Or categorie = "riolering" Then
                xlWorkSheetProducten.Range("A20").Value = "Riolering"
                xlWorkSheetProducten.Range("A21").WrapText = True
                xlWorkSheetProducten.Range("A21").Rows.AutoFit()
                xlWorkSheetProducten.Range("A21").Value += "* " + artikelnaam + vbLf
                xlWorkSheetProducten.Range("B21").Value += hoeveelheid + vbLf
                xlWorkSheetProducten.Range("C21").Value += "€" + prijs + "/" + eenheid + vbLf
                xlWorkSheetProducten.Range("D21").Value += totaalprijs + vbLf
                xlWorkSheetProducten.Range("A20").Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingle

As you can see in the picture, is cell 19 for the total of the products. Sorry for the language, i hope it doesn't bother you guys.

But due the multiline cell 17, it wouldn't let me sum up these prices, how is that possible? And is there a way to fix that?
Or is it in the code, that i did something wrong.

I hope you guys can help me, and if you need more information, i'll give it to you.

Gz!

Jaspertje

[westconn1]

is there some special reason for using multi line cells, rather than just using many lines?

you can try like

Function mlsum(cel As Range)
Dim t As Double, i As Integer, s() As String
s = (Split(cel, vbLf))
For i = 0 To UBound(s)
    If IsNumeric(s(i)) Then t = t + s(i)
Next
mlsum = t
End Function

put the above in std module

in cell D19 put =mlsum(D17) or whatever column

[Jaspertje]

is there some special reason for using multi line cells, rather than just using many lines?

you can try like

Function mlsum(cel As Range)
Dim t As Double, i As Integer, s() As String
s = (Split(cel, vbLf))
For i = 0 To UBound(s)
    If IsNumeric(s(i)) Then t = t + s(i)
Next
mlsum = t
End Function

put the above in std module

in cell D19 put =mlsum(D17) or whatever column

Hello pete,

Unfortunatelly it doesn't work. The result is in D19(in this case of the post) "#NAME?"

Maybe another way i could do it is maybe in worths:

1. Count the rows in Dgv
2. Check which categorys are used
3. Sort categorys
4. Adjust them among each other in the right cells
-> This will give the result that every product is in a single cell
5. dynamic last row will give the total, because SUM is working.

I know it is easy in my mind, but i have problems working out points 2, 3, 4.

Or, you could check your mlsum function once more, and maybe that is the problem.

I hope to hear from you.

[vbfbryce]

I tested Pete's function. When I have some actual numbers in a multi-line cell, I do get the correct sum as the answer. When I have something that includes text (like the "/Ps"), I get zero for the answer. In neither case do I get #Name. Can you zip and attach your workbook?

[Jaspertje]

I tested Pete's function. When I have some actual numbers in a multi-line cell, I do get the correct sum as the answer. When I have something that includes text (like the "/Ps"), I get zero for the answer. In neither case do I get #Name. Can you zip and attach your workbook?

Where did you put your function? I've putted it in my VB.net application, not in excel.
I attached my excel file, you'll see 3 sheets,
first one is intro, which is the frontpage of the qoutation.
The second one is producten, which is qoutation for products,
the last one is diensten, which is the qoutation for services.

Maybe the problem is that when the data got exported, it doesn't recorgnize that is a currency?
I would appreciate your input!

ZIP.zip

[vbfbryce]

My function is in Excel. That's why you get #Name, if yours isn't in Excel. Are you wanting to do the math in .Net, then populate the result? That can be done. If that's what you want, you should probably have this thread moved to the .Net forum.

[TnTinMN]

Jaspertje,

Is the DGV bound or unbound" i.e. did you set the DGV.DataSource property?

If it bound, is the data source a DataTable instance? If it is, then you can query the table for each category and then export the information to Excel.

If the DGV is unbound, then things get a bit more complicated as you will need to write the filtering code.

[westconn1]

if you want the function to work from .net, you can not use it as a worksheet function (worksheetfunctions must be in the workbook or an addin), the same function should work from .net as normal function
call like

range("d19").value = mlsum(range("d17"))

provide full qualification to the ranges

you may need to do some type conversions for .net