Convert Numbers into Text?

[robgolf]

I would like to take all numbers in column C of my spreadsheet and turn them into text based on the text value I gave each number.

Ex: I would type the number "1" into cell C1 and excel would automatically convert it into the word "New York". If I typed "2" into a cell it would convert in into the text "Chicago".

Thanks so much guys, any help is greatly appreciated!

-Rob

 

[jolivanes]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        If Target = 1 Then Target = "New York"
        If Target = 2 Then Target = "Chicago"
        If Target = "L" Then Target = "Apple"
    End If
End Sub

What is the other Worksheet_Change code suppose to do?

 

[robgolf]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        If Target = 1 Then Target = "New York"
        If Target = 2 Then Target = "Chicago"
        If Target = "L" Then Target = "Apple"
    End If
End Sub

What is the other Worksheet_Change code suppose to do?

OK THANKS! The other worskeet_change code is supposed to create a timestamp (date, hour, minute) in column D when someone fills out something in column C. Ex: If I type "1" into cell C2 in Cell D2 it will now read "3/30/12 11:25AM" at that exact instant.

 

[shg]

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 3 Then
            On Error GoTo Oops
            Application.EnableEvents = False
 
            .Offset(,1).Value = Now
 
            Select Case .Value
                Case 1
                    .Value = "New York"
                Case 2
                    .Value = "Chicago"
                Case "L"
                    .Value = "Apple"
            End Select
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[jolivanes]

@shg

Would that not enter the date in column D for any entry in Column C?

 

[shg]

It would.

If you wanted only "converted" values to receive a date stamp, then you'd need to move the statement into each Case paragraph.

And you might also want to check if the target cell is blank and clear the date stamp.

 

[jolivanes]

Something like this I assume you mean?

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 3 Then
            On Error GoTo Oops
            Application.EnableEvents = False
  
            Select Case .Value
                Case 1
                    .Value = "New York"
                    Target.Offset(, 1).Value = Now
                Case 2
                    .Value = "Chicago"
                    Target.Offset(, 1).Value = Now
                Case "L"
                    .Value = "Apple"
                    Target.Offset(, 1).Value = Now
            End Select
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

 

[robgolf]

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 3 Then
            On Error GoTo Oops
            Application.EnableEvents = False
 
            .Offset(,1).Value = Now
 
            Select Case .Value
                Case 1
                    .Value = "New York"
                Case 2
                    .Value = "Chicago"
                Case "L"
                    .Value = "Apple"
            End Select
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

Thanks for your help! This worked great!

 

[shg]

@ jolivanes: Yes, except for adding the case where the cell is empty to clear the date stamp, if desired.

@ robgolf: You're welcome, good luck.

 

[jolivanes]

@shg
I know it is not my thread but it is for robgolf's problem.

Would this be acceptable?

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 3 Then
            On Error GoTo Oops
            Application.EnableEvents = False
 
            '.Offset(, 1).Value = Now
 
            Select Case .Value
                Case 1
                    .Value = "New York"
                    Target.Offset(, 1).Value = Now
                Case 2
                    .Value = "Chicago"
                    Target.Offset(, 1).Value = Now
                Case "L"
                    .Value = "Apple"
                    Target.Offset(, 1).Value = Now
                Case ""
                    Target.Offset(, 1).Value = ""
            End Select
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub

It works in my test but I don't know if there would be any future problems caused by this.

 

[shg]

Maybe. It really depend on the logic the OP wants.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 3 Then
            On Error GoTo Oops
            Application.EnableEvents = False
 
            .Offset(, 1).ClearContents
 
            Select Case .Value
                Case 1
                    .Value = "New York"
                    .Offset(, 1).Value = Now
                Case 2
                    .Value = "Chicago"
                    .Offset(, 1).Value = Now
                Case "L"
                    .Value = "Apple"
                    .Offset(, 1).Value = Now
            End Select
Oops:
            Application.EnableEvents = True
        End If
    End With
End Sub