Funky Formula to VBA conversion

[babsc01]

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(D 2="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!

[SJ McAbney]

I don't know how to submit VBA tags.

Now you do! (http://www.vbaexpress.com/forum/faq.php?faq=vb_read_and_post#faq_vb_vbcode_toolbar) ;)

[Anne Troy]

And a more definitive version of using VBA tags is in my signature. :)

[babsc01]

Thanks folks...I'll post them correctly from here on out.

[Zack Barresse]

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


Hey babs, what do you mean by this? Are you pasting your code into a module?

[babsc01]

Zack...not from a module, just from a notepad file. I've tried several things to paste them in from Excel, such as using the formula as a NAME, but haven't really seen them through, since pasting does the trick.

[Zack Barresse]

Okay, so I'm confused. You're taking your information from a notepad file, gotcha; but what is it your copying? The formula? The code?

And lastly, got where it's coming from, but where are you pasting it to? Exactly where?

[babsc01]

I have this formula in a notepad file, and I paste it into the blank cell where I need it. I know there are better ways than that, but that's where I am. Cell E2 is always where I paste it.

[Zack Barresse]

Why not just use the formula instead of making a UDF for it? :dunno



Sub enterMyFormula()
With [E2]
.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(D 2=""CONNIE"", _
D2=""DARLENE"",D2=""MICHAEL"",D2=""MARIANO"",D2=""JAMES""),F2,D2))))"
End With
End Sub

Just need to add the double quotes in lieu of VBA recognition practices, but other than that, should work. Let me know if this works for ya.

[Anne Troy]

Hi, Babs. There's another thing a little unique about our forum. We let you edit your posts long after others would disallow. But I edited your post for you. We're not sticklers about it, and don't get upset or anything...just makes it easier to read. :)

[Greg T]

This appears to be returning the same results as the formula (more or less). I'm not falling through to the lookup for Connie, et al, so either I didn't figure out what to put in for test data or the logic is such that it never gets there.

HTH


Function AltEngName(varName As Variant, varShip As Variant) As Variant
On Error Resume Next
If varName = varShip Then
AltEngName = varName
ElseIf varName = vbNullString Then
AltEngName = varShip
ElseIf Len(Replace(Trim(varName), " ", " ")) = Len(Trim(varName)) Then
AltEngName = Application.WorksheetFunction.Lookup([D2], [Sheet2!$D$2:$D$110], [Sheet2!$A$2:$A$110])
ElseIf Not IsError(Application.WorksheetFunction.Match(varName, _
Array("CONNIE", "DARLENE", "MICHAEL", "MARIANO", "JAMES"))) Then
AltEngName = varShip
Else
AltEngName = varName
End If
End Function

[babsc01]

Never thought it could be that easy. Zack...thanks for the SUB suggestion...will use it religiously from now on. DB...thanks for the edit (it does look much better). Greg...I will try the VBA momentarily.

This site is awesome!

[Zack Barresse]

...thanks for the SUB suggestion...will use it religiously from now on.

One suggestion might be to assign a shortcut key to it or put it on a toolbar button for fast access to it. Especially if you're going to use it all the time, the shortcut key may just be the way to go. :yes


Greg...I will try the VBA momentarily.

UDF's definitely have their place, and that would definitely shorten up your current formula! ;)


This site is awesome!

Glad you like it! (Thank DB for starting it!)

Take care!

[babsc01]

I think this one can be closed...I've assigned all of my misc. formulas to macros. Much better than cutting/pasting into cells. Will add toolbars in a little bit. Thanks, Zack!

[Zack Barresse]

No problem, glad you got it sorted!

As for a direction you may want to take this, especially for future posting, is to make a custom menu out of this, so you can choose which formula you want very quickly from a drop down menu. An option, maybe worth your time if you've got bunches of formulas to do this with. :)

Take care!