Funky Formula to VBA conversion

Printable View

07-21-2004, 08:36 AM
babsc01

Funky Formula to VBA conversion

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

Code:

=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:

Code:

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!
07-21-2004, 08:47 AM
SJ McAbney

Quote:

Originally Posted by babsc01

I don't know how to submit VBA tags.

Now you do! ;)
07-21-2004, 08:53 AM
Anne Troy

And a more definitive version of using VBA tags is in my signature. :)
07-21-2004, 08:54 AM
babsc01

Thanks folks...I'll post them correctly from here on out.
07-21-2004, 08:59 AM
Zack Barresse

Quote:

Originally Posted by babsc01

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?
07-21-2004, 09:03 AM
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.
07-21-2004, 09:05 AM
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?
07-21-2004, 09:08 AM
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.
07-21-2004, 09:12 AM
Zack Barresse

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

Code:

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(D2=""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.
07-21-2004, 09:13 AM
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. :)
07-21-2004, 09:24 AM
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

Code:

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
07-21-2004, 10:36 AM
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!
07-21-2004, 10:42 AM
Zack Barresse

Quote:

Originally Posted by babsc01

...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

Quote:

Greg...I will try the VBA momentarily.

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

Quote:

This site is awesome!

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

Take care!
07-21-2004, 11:55 AM
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!
07-21-2004, 12:04 PM
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!