Try this:

Function SumValuesInCell(WrappedText As String) As Double
Dim Vals As Variant
Dim sum As Double, i As Long

WrappedText = Replace(WrappedText, vbCr, "")
Vals = Split(WrappedText, vbLf)
For i = 0 To UBound(Vals)
sum = sum + Val(Trim(Vals(i)))
Next i
SumValuesInCell = sum
End Function

Function SumAll(R As Range) As Double
Dim cl As Range
Dim sum As Double
For Each cl In R.Cells
sum = sum + SumValuesInCell(cl.Value)
Next cl
SumAll = sum
End Function

Enter this into a general code module. Then in your example,
=SumAll(A1:A4) used as a user-defined spreadsheet function (put say in
A5) will yield 3991 (as it should). It could also be called in another
macro via something like SumAll(Range("A1:A4"))

This code works in Windows. It will also work in MAC OS X and above. If
for some reason you are using Excel in Macs with an older operating
system this code won't work because of the way those systems handled
newlines.

Hope that helps

-John Coleman

bijan wrote:
I have a huge data that some of them contain wrap text cells with number value,
how I can sum this example correctly with macro?
A B
1 10
20
2 54
3 3365
18
4 524

kul

--
Don Guillett
SalesAid Software

"John Coleman" wrote in message
ups.com...
Try this:

Function SumValuesInCell(WrappedText As String) As Double
Dim Vals As Variant
Dim sum As Double, i As Long

WrappedText = Replace(WrappedText, vbCr, "")
Vals = Split(WrappedText, vbLf)
For i = 0 To UBound(Vals)
sum = sum + Val(Trim(Vals(i)))
Next i
SumValuesInCell = sum
End Function

Function SumAll(R As Range) As Double
Dim cl As Range
Dim sum As Double
For Each cl In R.Cells
sum = sum + SumValuesInCell(cl.Value)
Next cl
SumAll = sum
End Function

Enter this into a general code module. Then in your example,
=SumAll(A1:A4) used as a user-defined spreadsheet function (put say in
A5) will yield 3991 (as it should). It could also be called in another
macro via something like SumAll(Range("A1:A4"))

This code works in Windows. It will also work in MAC OS X and above. If
for some reason you are using Excel in Macs with an older operating
system this code won't work because of the way those systems handled
newlines.

Hope that helps

-John Coleman

bijan wrote:
I have a huge data that some of them contain wrap text cells with number
value,
how I can sum this example correctly with macro?
A B
1 10
20
2 54
3 3365
18
4 524

Thank you, It worked.
"Don Guillett" wrote:

kul

--
Don Guillett
SalesAid Software

"John Coleman" wrote in message
ups.com...
Try this:

Function SumValuesInCell(WrappedText As String) As Double
Dim Vals As Variant
Dim sum As Double, i As Long

WrappedText = Replace(WrappedText, vbCr, "")
Vals = Split(WrappedText, vbLf)
For i = 0 To UBound(Vals)
sum = sum + Val(Trim(Vals(i)))
Next i
SumValuesInCell = sum
End Function

Function SumAll(R As Range) As Double
Dim cl As Range
Dim sum As Double
For Each cl In R.Cells
sum = sum + SumValuesInCell(cl.Value)
Next cl
SumAll = sum
End Function

Enter this into a general code module. Then in your example,
=SumAll(A1:A4) used as a user-defined spreadsheet function (put say in
A5) will yield 3991 (as it should). It could also be called in another
macro via something like SumAll(Range("A1:A4"))

This code works in Windows. It will also work in MAC OS X and above. If
for some reason you are using Excel in Macs with an older operating
system this code won't work because of the way those systems handled
newlines.

Hope that helps

-John Coleman

bijan wrote:
I have a huge data that some of them contain wrap text cells with number
value,
how I can sum this example correctly with macro?
A B
1 10
20
2 54
3 3365
18
4 524

Thank you John

"John Coleman" wrote:

Try this:

Function SumValuesInCell(WrappedText As String) As Double
Dim Vals As Variant
Dim sum As Double, i As Long

WrappedText = Replace(WrappedText, vbCr, "")
Vals = Split(WrappedText, vbLf)
For i = 0 To UBound(Vals)
sum = sum + Val(Trim(Vals(i)))
Next i
SumValuesInCell = sum
End Function

Function SumAll(R As Range) As Double
Dim cl As Range
Dim sum As Double
For Each cl In R.Cells
sum = sum + SumValuesInCell(cl.Value)
Next cl
SumAll = sum
End Function

Enter this into a general code module. Then in your example,
=SumAll(A1:A4) used as a user-defined spreadsheet function (put say in
A5) will yield 3991 (as it should). It could also be called in another
macro via something like SumAll(Range("A1:A4"))

This code works in Windows. It will also work in MAC OS X and above. If
for some reason you are using Excel in Macs with an older operating
system this code won't work because of the way those systems handled
newlines.

Hope that helps

-John Coleman

bijan wrote:
I have a huge data that some of them contain wrap text cells with number value,
how I can sum this example correctly with macro?
A B
1 10
20
2 54
3 3365
18
4 524

You're welcome.
An optimization: the Trim() function is playing no role whatsoever. I
often use it when dealing with string input as a form of defensive
programming but in this case it is defending against nothing since
Val() seems to be indifferent to any leading or trailing whitespace (as
a bit of experimenting in the immediate window shows). Thus the line
sum = sum + Val(Trim(Vals(i)))
could be replaced by
sum = sum + Val(Vals(i))
without changing the functionality. It could make a slight but
noticable difference in calculation time if used with large data sets.

Happy New Year

-John Coleman

bijan wrote:
Thank you John