adding pounds and ounces

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

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

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

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

Hi Toppers

Thanks a bunch, I will go and play.

Greg

"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

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" 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

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


"Peter Rooney" wrote:

> 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" 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

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

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



"Toppers" wrote:

> 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