Hi,
Modified version and in my earlier version there was a typo - the
last line before "end function" should be sumlboz = nlb & "lb " & nz & "oz"
not sumlbz (sorry!)

Function sumlboz(ByVal rng As Range) As String

nlb = 0
nz = 0
For Each Cell In rng
n = InStr(1, Cell, "lb")
lbs = CInt(Left(Cell, n - 1))
Cell = Mid(Cell, n + 2, 256)
n = InStr(1, Cell, "oz")
ozs = CInt(Left(Cell, n - 1))
nlb = nlb + lbs
nz = nz + ozs
Next
nlb = nlb + nz \ 16
nz = nz Mod 16

sumlboz = nlb & "lb " & nz & "oz"

End Function


"Toppers" wrote:

Hi,
You can call it in VBA:

MyAnswer=Sumlboz(Range("A1:a10")

or Range("b1")=Sumlboz(Range("A1:a10")

or you could place it in a cell as a User Defined Function (UDF):

So in B1 put "=sumlboz(a1:a10)" (without the quotes)

HTH

"Model-man" wrote:

Hi Toppers

Thanks for such a prompt reply. I am a bit of a novice, so could you tell me
If I call this as a macro.

Thanks again
Greg

"Toppers" wrote:

Hi,

try this which assumes a cell contains the lb/oz data:


Function sumlboz(ByVal rng As Range) As String
Dim v As Variant
nlb = 0
nz = 0
For Each cell In rng
v = Split(cell, " ")
n1 = InStr(1, v(0), "lb")
lbs = CInt(Left(v(0), n1 - 1))
n2 = InStr(1, v(1), "o")
ozs = CInt(Left(v(1), n2 - 1))
nlb = nlb + lbs
nz = nz + ozs
Next
nlb = nlb + nz \ 16
nz = nz Mod 16
sumlbz = nlb & "lb " & nz & "oz"
End Function
Sub test()
MsgBox sumlboz(Range("b1:d1"))
End Sub


"Model-man" wrote:

Hi
Can anybody tell me how to add a row of cell in pounds and ounces.
I would prefer the to show the row like this as it will be seen by others.

32lb 40z 20lb 2oz 16lb 8oz = 68lb 14oz

Is it possible to have text and data in the same cell, but not to show the
data.

Thanks