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

 

[T. Valko]

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

See if this helps:

http://contextures.com/xlFunctions02.html

 

[jproffer]

If that's the only 2, put this into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$1" Then
        If Target = 1 Then Target = "New York"
        If Target = 2 Then Target = "Chicago"
    End If
End Sub

 

[robgolf]

If that's the only 2, put this into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$1" Then
        If Target = 1 Then Target = "New York"
        If Target = 2 Then Target = "Chicago"
    End If
End Sub

After I open up the VBA editor should I go to insert module, paste that code in and then hit save? I tried that and it did not work. I am new to VBA so I would need further guidance. Thanks so much!

 

[jproffer]

Nope, right click on the sheet tab and click "View Code". Paste it in there.

 

[robgolf]

That did not work. Any ideas? I typed in "1" into column C and it did not change to text, it just stayed as "1".

Thanks again!

Nope, right click on the sheet tab and click "View Code". Paste it in there.

 

[jolivanes]

Did you type in a 1 in Cell C1?

 

[robgolf]

Did you type in a 1 in Cell C1?

Looks like it works if you type it into cell C1 but I need it to work all the way down to C9000, any ideas?

Thanks !!

 

[jolivanes]

Change

If Target.Address = "$C$1" Then

to

If Target.Column = 3 Then

or to this

If Not Intersect(Target, Range("C:C")) Is Nothing Then

That should work anywhere in Column C

HTH

 

[robgolf]

Change

If Target.Address = "$C$1" Then

to

If Target.Column = 3 Then

or to this

If Not Intersect(Target, Range("C:C")) Is Nothing Then

That should work anywhere in Column C

HTH

OK GREAT! That line of code worked for all of column C.

The issue now lies in the fact that I have two "Worksheet Change" items in the same line of code and it causes and it says "Ambiguous name detected" when I try to write something in column C.

Here is my code:

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"
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("C:C").Column Then
Cells(.Row, "D").Value = Now()
End If
End With
Next Cell
End Sub

Any thoughts on how I can have two separate worksheet changes without causing an error?

Last Question: If I wanted to take a letter such as "L" and have it automatically convert to a word such as "Apple" for all of Column C how would one go about doing that? I tried using the code you supplied me with above but I think that only works for numbers when declaring a target.

Thanks again!