adding pounds and ounces

[Model-man]

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

 

[Toppers]

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]

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]

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]

Hi Toppers

Thanks a bunch, I will go and play.

Greg

 

[Peter Rooney]

Good morning!
Is there any specific way in which the entries should be typed in, in order
for them to be odded correctly by this function? i.e. "3lb5oz", "3lb 5oz", "3
lb 5 oz" etc?

I just get #VALUE! when I try to use it.

Thanks in advance.

Pete

 

[Toppers]

Peter,
I planned on data being of the form 10lb*5oz i.e no space
between the value and units BUT one space as indicated by the asterisk. If
this is too restrictive, then the logic could be changed to be more flexible

 

[Toppers]

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

 

[Peter Rooney]

Toppers,

Thanks - I added an if len(cell)>0 statement within the for loop to take
account of any blank cells in the range being added.

Thanks for coming back to me, especially when it wasn't my question in the
first place!

Pete