if Range("A1") = "Mike" Then Range("A1") = "michael" Then Exit Sub

Hi

I have a bit of an issue.

I have a long list of if statements that are basically as follows:

if Range("A1") = "Mike" Then Range("A1") = "michael"
if Range("A1") = "Matt" Then Range("A1") = "matthew"

etc

This list has 40+ names that are defined as something (ie Mike)(taken from external data) but need to be something else (ie michael)

What I want to do is the following:

if Range("A1") = "Mike" Then Range("A1") = "michael" Then ExitSub

to avoid the macro going through all 40+ if statements everytime

My current get around is the following

If Range("A1") = "Mike" Then
    Range("A1") = "michael"
    Exit Sub
End If

but it takes up huge amounts of space in the code and probably isnt the most effiecient way of doing it. Plus it doesnt look very good

Does anyone know a simple work around?

Something like the following, incorporated into your sheet?

Sub haha()
    Select Case Range("A1").Value
        Case "Mike": Range("A1").Value = "michael"
        Case "Matt": Range("A1").Value = "matthew"
    End Select
        
        
End Sub

Cheers BigBas

Works perfectly

For the record, I do want to state that this seems like an overly complicated bit of code (over 40 names, each tied to a specific cell), and it would seem more logical to store the information in a "database sheet" of sorts. If you want help with implementation, let us know and we will do our best to help.