VBA Code Required For Conversion

[hrayani]

Hello,

I need a VBA code for the following, if possible.


CELL A1 90.00 ------> centimeters
CELL A2 35.43 -------> Inches
CELL A3 00.90 --------> Meter
CELL A4 02.95 ---------> Feet


What i require is if i change the value in cell A1 then all the rest 3 cells should show the converted results based on that manually entered cell. And If i change the value in cell A2 then all the rest 3 cells should show the converted results based on that manually entered cell and so on. hope anyone can provide a solution.

Thanks,

Humayun

 

[oldbrewer]

you cannot have A2 dependent on A1 AND A1 dependent on A2

what you can do is start with all cells blank, put a value in one of them and use a simple macro to populate the otherr 3 - is this acceptable

 

[mikerickson]

You could use the CONVERT function

=CONVERT(A1,"cm","in")
=CONVERT(A1,"cm","m")
=CONVERT(A1,"cm","ft")

Edit: never mind, you can't use Excel functions to set up a circular reference situation like this.

You'd have to use VBA event code in the sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 And .Column = 1 And .Row < 5 Then
            Application.EnableEvents = False
            Select Case .Row
                Case 1
                    Me.Cells(2, 1) = WorksheetFunction.Convert(.Value, "cm", "in")
                    Me.Cells(3, 1) = WorksheetFunction.Convert(.Value, "cm", "m")
                    Me.Cells(4, 1) = WorksheetFunction.Convert(.Value, "cm", "ft")
                Case 2
                    Me.Cells(1, 1) = WorksheetFunction.Convert(.Value, "in", "cm")
                    Me.Cells(3, 1) = WorksheetFunction.Convert(.Value, "in", "m")
                    Me.Cells(4, 1) = WorksheetFunction.Convert(.Value, "in", "ft")
                Case 3
                    Me.Cells(1, 1) = WorksheetFunction.Convert(.Value, "m", "cm")
                    Me.Cells(2, 1) = WorksheetFunction.Convert(.Value, "m", "in")
                    Me.Cells(4, 1) = WorksheetFunction.Convert(.Value, "m", "ft")
                Case 4
                    Me.Cells(2, 1) = WorksheetFunction.Convert(.Value, "ft", "cm")
                    Me.Cells(3, 1) = WorksheetFunction.Convert(.Value, "ft", "in")
                    Me.Cells(4, 1) = WorksheetFunction.Convert(.Value, "ft", "m")
            End Select
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[hrayani]

thanks mikerickson

i am already working on the formula you provided... thanks once again. but everytime i dont have the data in centimeters. sometimes i have it in inches, sometime in meters and so on. so, if i will enter the formula in cells A2 to A3 then i would not be able to enter the data in it.

 

[mikerickson]

Here's a corrected version that does the conversion directly and has the cell references corrected.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const cm2in As Double = 0.393700787401575
    With Target
        If .Cells.Count = 1 And .Column = 1 And .Row < 5 Then
            Application.EnableEvents = False
            Select Case .Row
                Case 1
                    Me.Cells(2, 1) = .Value * cm2in
                    Me.Cells(3, 1) = .Value / 100
                    Me.Cells(4, 1) = .Value * cm2in / 12
                Case 2
                    Me.Cells(1, 1) = .Value / cm2in
                    Me.Cells(3, 1) = .Value / cm2in / 100
                    Me.Cells(4, 1) = .Value / 12
                Case 3
                    Me.Cells(1, 1) = .Value * 100
                    Me.Cells(2, 1) = .Value * 100 * cm2in
                    Me.Cells(4, 1) = .Value * 100 * cm2in / 12
                Case 4
                    Me.Cells(1, 1) = .Value * 12 / cm2in
                    Me.Cells(2, 1) = .Value * 12
                    Me.Cells(3, 1) = .Value * 12 / cm2in / 100
            End Select
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[hrayani]

dear mike, well i was not sure where to enter the code.... i mean in the sheet code or module....

well i entered in both ..... but nothing is happening... i mean i entered 100 in cell A1 but cell A2 to A4 remains blank

any Idea ???

 

[hrayani]

i also tried the second code which you provided.. problem remains the same... nothing is happening..

 

[hrayani]

sorry, i made some mistake.... its working just perfect

thanks

 

[hrayani]

dear mike can you just ammend the coded a bit... i need feet conversion in the third cell and Meter conversion in the fourth cell... at the moment its vice versa

 

[oldbrewer]

or this is another way

in

89.9922

cm

35.43

in

35.43

< < < < start value

CELL A1 90.00 ------> centimeters

0.899922

metres

CELL A2 35.43 -------> Inches

2.9525

feet

CELL A3 00.90 --------> Meter

CELL A4 02.95 ---------> Feet

col J

row 16

in

cm

metres

feet

in

2.54

0.0254

0.083333

cm

0.393701

0.01

0.032808

metres

39.37008

100

3.28084

feet

12

30.48

0.3048

when you enter a number in C2:C5

C1 automatically detects the units to its left

the formula in col A uses the conversion table

to calculate the required numbers

formula in A2

=IF(C2="",LARGE($C$2:$C$5,1)*OFFSET($J$16,MATCH($C$1,$J$17:$J$20,0),MATCH(B2,$K$16:$N$16,0)),C2)

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>