Hello all...I have a formula I'm trying to convert to VBA. Here's the formula:

=IF(D2=F2,D2,IF(ISBLANK(D2),F2,IF((LEN(SUBSTITUTE(TRIM(D2),CHAR(32),CHAR(32)&CHAR(32)))-LEN(TRIM(D2))+1)=1,LOOKUP(D2,Sheet2!$D$2:$D$110,Sheet2!$A$2:$A$110),IF(OR(D2="CONNIE",D2="DARLENE",D2="MICHAEL",D2="MARIANO",D2="JAMES"),F2,D2))))
In a nutshell, it says this:

1) If NAME=SHIP then show NAME
2) If NAME is null then show SHIP
3) If NAME is an IDnumber then LOOKUP name from Sheet2
4) If NAME is one of 5 people then show SHIP
5) All others show NAME

In the conversion, I know the SUBSTITUTE worksheet function won't cut it.

Here's what I've tried:

Function AltEngName (Name, Ship)
If Name=Ship then 
    AltEngName=Name
Else If IsNull(Name) then 
   AltEngName=Ship
Else If LEN(SUBSTITUTE(TRIM(D2),CHAR(32),CHAR(32)&CHAR(32)))-LEN(TRIM(D2))+1)=1 Then
    AltEngName = LOOKUP(D2,Sheet2!$D$2:$D$110,Sheet2!$A$2:$A$110)
Else If Name In ("CONNIE","DARLENE","MICHAEL","MARIANO","JAMES") Then
    AltEngName = Ship
Else AltEngName = Name
End If
End Function
Sorry, I don't know how to submit VBA tags.

The FORMULA works every time, but I have to paste it in from a text file every time to use it.

I'm also relatively new to coding, so any advice in "cleaning up" the code would be greatly appreciated.

Thanks!