Comparing Apples and Oranges, by the Kilogram or by the Pound

[ChrisGreaves]

Code: Select all

Function sngKg(rng As Range) As Single
    Dim strText As String
    strText = UCase(rng.Text)
    If Right(strText, 1) = "P" Then
        sngKg = Left(strText, Len(strText) - 1) / 2.2
    Else
        If Right(strText, 1) = "K" Then
            sngKg = Left(strText, Len(strText) - 1)
        Else
            sngKg = strText
        End If
    End If
'Sub TESTsngKg()
'    Debug.Print sngKg(ActiveCell)
'End Sub
End Function

Don't be discouraged by the VBA code; you only have to copy and paste it into a module, and Eileen's Lounge VBA forum will help you with that.

Here is a screen shot showing what the user-defined function can do for you:-

Untitled.png

I grew tired of entering the cost of groceries into a spreadsheet. Canada is metric, so half the time groceries are expressed in pounds, and the rest of the time in Kilograms. Do I convert in my head and hope I am right, do I try to remember that whole-wheat flour is in Kg but apples are by the pound? I have recently taken to just keying in the number and hoping, which predictably has led to some outlandish purchases.

I settled for a standard "kilograms" because I'm not from Texas, but you could easily flip the function around to settle on pounds avoirdupois even 'though you don't buy peas (Franglais Joke!)

I have elected to load the function to columns G and K, so now all I need do is glance at the receipt and tack the letter "p" to then end of the weight if it is expressed in pounds.

The function could be used in a GUI form, allowing the user to input a value as "5.5p" or as "5.5k" or just "5.5" if your default unit is Kilograms.

And yes, were on special this morning down at Foodland.
Cheers
Chris