VBA change cell value based on it's value

[ceytl]

Hi,

I am trying to write a VBA script to do the following:

In cell F1 there can be five different values.

I want the cell value to change to the following depending on the value in F1:

"1 New York" change to "21"
"2 LA" change to "22"
"3 Portland" change to "23"
"4 Chicago" change to "24"
"5 Miami" change to "25"

Any help would be appreciated!

 

[Joe4]

Try something like this:

Sub MyMacro()

    Select Case Range("F1")
        Case "New York"
            Range("F1") = 21
        Case "LA"
            Range("F1") = 22
        Case "Portland"
            Range("F1") = 23
        Case "Chicago"
            Range("F1") = 24
        Case "Miami"
            Range("F1") = 25
    End Select
    
End Sub

 

[FrancoisT]

Hello ceytl, I'm a bit rusty with my VBA but I think this should do the trick:

First you have to declare a private sub in your sheet with the "SelectionChange" event and paste a code like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not (Intersect(Target, Range("F1")) Is Nothing) Then
    Call ChangeValue
    End If
End Sub

Then you have to create a regular Sub such as:

Sub ChangeValue()
    If Range("F1").Value = "New York" Then
        Range("B1").Value = 21
    ElseIf Range("F1").Value = "LA" Then
        Range("B1").Value = 22
    ElseIf Range("F1").Value = "Portland" Then
        Range("B1").Value = 23
    ElseIf Range("F1").Value = "Chicago" Then
        Range("B1").Value = 24
    ElseIf Range("F1").Value = "Miami" Then
        Range("B1").Value = 25
    Else
        Range("B1").Value = "Out of range"
    End If
End Sub

This way when you change the value of F1 it will trigger the code and change the value of the target cell.

I hope it helps, have a great day.

 

[rlv01]

Slight variation.

'Cell value in F1 is changed depending on the value in F1
Sub ReplaceExample()
    With ActiveSheet.Range("F1")
        Select Case .Value
            Case "1 New York"
                .Value = 21
            Case "2 LA"
                .Value = 22
            Case "3 Portland"
                .Value = 23
            Case "4 Chicago"
                .Value = 24
            Case "5 miami"
                .Value = 25
        End Select
    End With
End Sub

 

[Joe4]

FranciosT,

If you wanted to do it automatically, there are a few things to note:
1. You have it running against "Worksheet_SelectionChange". That will run whenever F1 is selected. If you want to run it whenevewr F1 is manually updated, you would use "Worksheet_Change" instead.
2. There is no need to break it out into multiple procedures. You can do it all under the "Worksheet_SelectionChange" (or "Worksheet_Change") procedure.

 

[ceytl]

Try something like this:

Sub MyMacro()

    Select Case Range("F1")
        Case "New York"
            Range("F1") = 21
        Case "LA"
            Range("F1") = 22
        Case "Portland"
            Range("F1") = 23
        Case "Chicago"
            Range("F1") = 24
        Case "Miami"
            Range("F1") = 25
    End Select
   
End Sub

Thanks you!

 

[ceytl]

Hello ceytl, I'm a bit rusty with my VBA but I think this should do the trick:

First you have to declare a private sub in your sheet with the "SelectionChange" event and paste a code like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not (Intersect(Target, Range("F1")) Is Nothing) Then
    Call ChangeValue
    End If
End Sub

Then you have to create a regular Sub such as:

Sub ChangeValue()
    If Range("F1").Value = "New York" Then
        Range("B1").Value = 21
    ElseIf Range("F1").Value = "LA" Then
        Range("B1").Value = 22
    ElseIf Range("F1").Value = "Portland" Then
        Range("B1").Value = 23
    ElseIf Range("F1").Value = "Chicago" Then
        Range("B1").Value = 24
    ElseIf Range("F1").Value = "Miami" Then
        Range("B1").Value = 25
    Else
        Range("B1").Value = "Out of range"
    End If
End Sub

This way when you change the value of F1 it will trigger the code and change the value of the target cell.

I hope it helps, have a great day.

Thank you!

 

[ceytl]

Slight variation.

'Cell value in F1 is changed depending on the value in F1
Sub ReplaceExample()
    With ActiveSheet.Range("F1")
        Select Case .Value
            Case "1 New York"
                .Value = 21
            Case "2 LA"
                .Value = 22
            Case "3 Portland"
                .Value = 23
            Case "4 Chicago"
                .Value = 24
            Case "5 miami"
                .Value = 25
        End Select
    End With
End Sub

Thanks you!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[FrancoisT]

FranciosT,

If you wanted to do it automatically, there are a few things to note:
1. You have it running against "Worksheet_SelectionChange". That will run whenever F1 is selected. If you want to run it whenevewr F1 is manually updated, you would use "Worksheet_Change" instead.
2. There is no need to break it out into multiple procedures. You can do it all under the "Worksheet_SelectionChange" (or "Worksheet_Change") procedure.

Thanks for this great feedback Joe, my VBA is a bit rusty indeed since I switched to Python ?