Macro to change rep id

[mgiehm]

I am trying to create a macro that allows the user to input his or her name in cell C10, then take all possibilities and return their 3 letter initials. For instance if the user could put Mike, mike, michael, Michael and it would change it to MWG when the macro is run.

 

[JackDanIce]

Welcome to the board.

M = Mike or mike or Michael or michael

W = ?

G = ?

It's not clear where you derive WG from and where do you want the initials output to? You've only stated C10 as your input cell.

 

[mgiehm]

Sorry about that. I want their three letter initials to show up in the cell instead of whatever name they type. MWG being the initials of the rep whose first name is Mike. There is a few macros that are tagged along with this template that are written based on the reps initials. What I was thinking was something like

IF Range("C10")="Mike" Or Range("C10")="Michael" Or Range("C10")="mike" Then

Range("C10")="MWG"
Else 
If Range("C10")="Brian" Or Range("C10")="brian" Then

Range("C10")="BJD"
End if
End If

Seems like a ton of if then statements for a handful of reps. Any ideas are appreciated.

 

[JackDanIce]

Well one way to simplify would be to change below to:

If UCase(Range("C10")) = "MIKE" Or UCase(Range("C10")) = "MICHAEL" Then
  Range("C10") = "MWG"
End If

However, assuming you have more than two reps, I think you'd be better using a SELECT CASE statement, i.e.

With Range("C10")
    Select Case UCase(.Value)
        Case "MIKE", "MICHAEL"
            .Value = "MWG"
        Case "BRIAN"
            .Value = "BJD"
        Case "WILLIAM", "BILL"
            .Value = "WMS"
        Case Else
            .Value = .Value
    End Select
End With

By making all the values in capital letters (Upper case), you drop from 4 possibles (e.g. mike, Mike, Michael, Michael or even MiKE or mIKe etc) to just two MIKE or MICHAEL.

CASE statements are much better to use than multiple/nested IF statements when you have more then 2 criteria to consider

 

[mgiehm]

You my friend are a genius. I thank you for your fast response. It is working perfectly. Thank you ray:ray:ray:ray:

 

[JackDanIce]

You're welcome, enjoy playing around with that