# Weight Stones, Lbs, Ounces to lbs/Ounces

• Hi All,

Is there a way to format a cell so that I can enter a "weight" i.e. 15 Stone 8 pounds and 3 ounces as 15.8.3 and then have a conversion cell which calculates this as pounds and ounces? i.e. 218 lbs 3 ounces or 218.3 (the figure after the decimal point ".3" is measured in 16ths to represent 3/16)

Thank you

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

How are you arriving at 218 pounds from 15.8 ? I understand the .3 = 3 ounces.

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

stone * lbs + ounces
15 * 14 = 210 + 8 = 218 + the 3 ounces

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

Paste this into a Routine Module :

To use, enter your numbers into A1, make certain A1 is the active cell, click the command button.

Do the same thing for each cell in Col A.

I tried to make the macro auto-execute by placing it in the Sheet_Change or Sheet_SelectionChange events. I also tried adding a Key Press call "~" (the ENTER key) all to no avail.
I believe the issue is referencing the current cell having data entered being referred to as "Active Cell". When pressing ENTER, that cell is no longer the Active Cell ?

I'll keep experimenting but perhaps someone else has a solution to the Active Cell issue ? I'm all for someone else jumping in to assist so you can get your project going !

## Files

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

Hi

This would work apart from the need to reselect A1 after Data entry.
Using the code below works, but the cell is still changed on pressing enter and I et the calculation in Cell B2

I would like to be able to reselect cell A1 after data entry then run the module Addem() as per my 2nd sample below but it does not work?

Code
``````Private Sub Worksheet_Change(ByVal Target As Range)
End If
End Sub``````

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

Try pasting this into the Sheet level module :

## Files

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

Hi
Thank you for the response.

When I add this to Sheet1, I get an error highlighting the line .offset(, 1) ........
and Runtime Error , subscript out of range !

Any Thoughts

Thank you

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

I can't replicate the error you are seeing.

The last workbook posted in Post #6 runs fine here.
Copying the macro and pasting in to a new workbook ... it runs fine as well.

I'm not certain what to suggest at this point.

:0ops:

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

I have downloaded the workbook from post #6, works great for me too.....
Not too sure what the button is meant to do, the macro is not available...
JL can you give some more details? Have you got more code in there on top of what Logit has added?

R

BTW: If the reply someone gave helped you, it is nice to thank them :wink:. You can also recognize their contribution by using the reputation button!

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

The button is a hold over from the my original post. You can delete it.

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

Ok, I think I have found my error.
I tested this without entering 3 split values i.e. 10.0.0, I just entered 10 assuming the code would translate to 140

to prevent a user making the same error, can the cell be formatted to ensure the correct format is entered?

Thank you

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

oops, submitted twice and edited.

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

btw, I am running excel 2016 but have another laptop with 2013 installed, I tried it on excel 2013 and the same error appears ?

Thank you

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

Will the data always be in the format: two numbers / decimal / one number / decimal / one number ?

• Re: Weight Stones, Lbs, Ounces to lbs/Ounces

No, sorry for misleading previous post. The number should be entered as a maximum case with leading zeros where required i.e. 10.00.00 or 09.05.03
A zero should be entered by the user if the digit is single

A routine to check the format entered prior to converting, "with an error message if not" would be helpful ?

Very much appreciate the help

Thank you

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!