NEED EQUATION PLEASE

[Carolan]

Would like equation that says if A1 to A10 has 400 in it, the change 400 to
"James"
Could someone please help me.

 

[Gary''s Student]

You might need a macro for this:

Sub crln()
Set rr = Range("A1:A10")
For Each r In rr
If r.Value = 400 Then
r.Value = "James"
End If
Next
End Sub

 

[Carolan]

I'd rather do a formula if possible on a different sheet as I will have
several name. For example, 400 = James, 401 - John, 402 = Kevin. So there
could be all three numbers in a column.

 

[Max]

Maybe something like this

Assume numbers in Sheet1, in A1 down
In Sheet2, place in A1
=IF(Sheet1!A1="","",VLOOKUP(Sheet1!A1,{400,"James";401,"John";402,"Kevin"},2,0))
Copy down

 

[Carolan]

Max, that works except what I'm trying to do is change the number in A1 to
the resulting text in the same cell. In otherwords, if A1=400, change the
400 in A1 to "James" So if I type 400 in A1 it will automatically change it
to "James" as soon as I move to the next cell.

 

[David Biddulph]

As stated earlier, that needs a macro, not a formula.

 

[Ron Rosenfeld]

Max, that works except what I'm trying to do is change the number in A1 to
the resulting text in the same cell. In otherwords, if A1=400, change the
400 in A1 to "James" So if I type 400 in A1 it will automatically change it
to "James" as soon as I move to the next cell.

As has been written, you cannot do that with a formula. A formula can only
return a result to the cell in which it resides. It cannot change another
cell, nor can it change itself, yet persist within the cell.

Take another look at Gary's student macro.

Or, if you want this automatically, use an event macro.

To enter this, right click on the sheet tab and select View Code from the
dropdown menu. Then paste the code below into the window that opens.

=========================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range
Dim c As Range
'set up to change entries in Column A only
Set a = Range("A:A")
If Not Intersect(Target, a) Is Nothing Then
For Each c In Target
Select Case c
Case Is = 400
c.Value = "James"
Case Is = 401
c.Value = "John"
Case Is = 402
c.Value = "Kevin"
End Select
Next c
End If
End Sub
=================================
--ron

 

[Max]

If you want to keep it simple, you could always copy the formula col at the
end of the day, then overwrite the source col with a paste special as values

 

[Carolan]

Thanks everyone - Ron's solution worked perfectly for my needs.

 

[Gord Dibben]

In addition to Ron's "worked perfectly" solution.

If your needs changed to many of these items, you can add to the "nums" and
"vals" arrays and not use the many "Case is" statements.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A:A") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
On Error GoTo endit:
Application.EnableEvents = False
End If
nums = Array(400, 401, 402, 403) 'adjust to suit
vals = Array("James", "Mike", "Harold", "Oscar") 'adjust to suit
For Each rr In r
ival = 0
For i = LBound(nums) To UBound(nums)
If rr.Value = nums(i) Then
ival = vals(i)
End If
Next
If ival > 0 Then
rr.Value = ival
End If
Next
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP