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 :
Option Explicit
Sub addem()
'splits Text active cell using "." as separator
Dim splitVals As Variant
Dim totalVals As Long
Dim B1 As Integer
Dim c1 As Integer
Dim d1 As Integer
splitVals = Split(ActiveCell.Value, ".")
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value * 14 + ActiveCell.Offset(0, 2).Value & "." & ActiveCell.Offset(0, 3).Value
ActiveCell.Offset(0, 2).Value = ""
ActiveCell.Offset(0, 3).Value = ""
End SubTo 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 !
Re: Weight Stones, Lbs, Ounces to lbs/Ounces
Hi
Thanks for the reply.
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?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call addem
End If
End SubSub addem()
'splits Text active cell using "." as separator
Dim splitVals As Variant
Dim totalVals As Long
Dim B1 As Integer
Dim c1 As Integer
Dim d1 As Integer
splitVals = Split(ActiveCell.Value, ".")
totalVals = UBound(splitVals)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row, ActiveCell.Column + 1 + totalVals)).Value = splitVals
ActiveSheet.Range("A1").Select
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value * 14 + ActiveCell.Offset(0, 2).Value & "." & ActiveCell.Offset(0, 3).Value
ActiveCell.Offset(0, 2).Value = ""
ActiveCell.Offset(0, 3).Value = ""
End SubRe: Weight Stones, Lbs, Ounces to lbs/Ounces
Had to go get some help to help you. :smash:
Try pasting this into the Sheet level module :
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim splitVals
With Target
If .Column = 1 And Not .Cells.Count > 1 Then
'splits Text active cell using "." as separator
Application.EnableEvents = False
splitVals = Split(.Value, ".")
Application.EnableEvents = True
.Offset(, 1).Value = (splitVals(0) * 14) + splitVals(1) & "." & splitVals(2)
End If
End With
End SubRe: 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 !
Also tried downloading and running your sample sheet again, but the same error is present?
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?
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
Don’t have an account yet? Register yourself now and be a part of our community!