Convert inches to mm in cell A1 when value of cell B10 changes

[JimS63]

I have a cell whos value is changed when a button is clicked. This cell is B9 and the value is 1 if the German button is clicked and 2 if the English button is clicked
I have several cells that the user inputs a value into. This value is in inches if the English button is clicked and mm if the German Button is clicked

I would like a code that would do the following.
If Cell B9 changes from 1-2 then divide cells C1, C2, D3, D5 by 25.4
If Cell B9 changes from 2-1 then multiply cells C1, C2, D3, D5 by 25.4

I want the cell number to change but only when cell B9 changes value

I was going to use the Application.WorksheetFunction.Convert(c.Value, "in", "mm") to do this conversion but don't know how to create an array that includes non-contiguous cells and to only do this if the cell B9 changes from one value to the other.


There are probably 40 cells throughout the sheet that I need to put into the array but they never change location.

Any help would be greatly appreciated as I do this for length, weight and temperature values

 

[rpaulson]

how about this

Sub convert()

Dim rng As Range

Set rng = Range("C1, C2, D3, D5")

For Each cell In rng
    If [B9] = 2 Then cell.Value = cell.Value / 25.4
    If [B9] = 1 Then cell.Value = cell.Value * 25.4
Next

End Sub

hth,

Ross

 

[Joe4]

And if you want it to happen automatically when the value in B9 changes, you can use a Worksheet_Change event procedure macro to do that (building off of what Ross created), i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range

    Set rng = Range("C1, C2, D3, D5")

    If Target.Address = Range("B9").Address Then
        Application.EnableEvents = False
        For Each cell In rng
            If Target = 2 Then cell.Value = cell.Value / 25.4
            If Target = 1 Then cell.Value = cell.Value * 25.4
        Next cell
        Application.EnableEvents = True
    End If
       
End Sub

 

[JimS63]

And if you want it to happen automatically when the value in B9 changes, you can use a Worksheet_Change event procedure macro to do that (building off of what Ross created), i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range

    Set rng = Range("C1, C2, D3, D5")

    If Target.Address = Range("B9").Address Then
        Application.EnableEvents = False
        For Each cell In rng
            If Target = 2 Then cell.Value = cell.Value / 25.4
            If Target = 1 Then cell.Value = cell.Value * 25.4
        Next cell
        Application.EnableEvents = True
    End If
      
End Sub

Ross and Joe thank you very much. This is working almost perfectly.

I have a sub that is as follows

Sub Deutsch_Klicken()
    Sheets(1).Cells(9, 2).Value = 1    'Sprache Deutsch
End Sub
Sub English_Klicken()
    Sheets(1).Cells(9, 2).Value = 2    'Sprache Englisch
End Sub

This sets the value of B9.

When I click the same flag multiple times it will run the code you gave me.
So if I have a 1 in cell C1 when I click the German button it changes to 25.4 and if I click the German button again it changes to 645.16. Anyway to make this work only if it changes value?

 

[Joe4]

One way would be to also store the value in some ununsed cell (like say, AZ1).
Then have the macro compare the value in B9 to the value in AZ1. If it is the same, exit the Macro.
If it is different, then have it do all the stuff we have the code doing, and then update cell AZ1 for next time.