Complicated IF formula (how do I combine 11 if statements)

[alinka]

Hi Excel wizards,

I am a newbie to excel and have a complicated problem. See spreadsheet below for reference, plz.

This is what I need to do:

1) I regularly get a large amount of name data in columns A through C. Each row has an email syntax in Column D. In Column E, I created a formula that writes an email address for the corresponding syntax. I have 11 email syntaxes in total.

2) I need a formula that can read an email syntax in D, and use one of my 11 formulas to create an email address. In other words, I want the formula to do this:

a) IF we find "first.last" in D, use formula =A2&"."&C2&(MID(D2,(FIND("@",D2)),50))

b) IF we find "first_last" in D, use formula =A3&"_"&C3&(MID(D3,(FIND("@",D3)),50))

c) IF we find "first.m.last" in D, use formula =A4&"."&(IF(B4="","",(LEFT(B4,1))&"."))&C4&(MID(D4,(FIND("@",D4)),50))

and so on for the next 8 syntaxes that are shown in D.

So, there are 11 IF statements, that need to be combined into 1 formula. How can I go about doing this??? Or do you envision an easier way? Is it possible to write a vb code that would do this? ANY HELP IS APPRECIATED. THANKS!!!!

email-syntax.xls
	A	B	C	D	E
1	FirstName	Middle	LastName	EmailSyntax	EmailFormula
2	Alan	J	Lacy	first.last@company.com	Alan.Lacy@company.com
3	William	C	White	first_last@company.com	William_White@company.com
4	Michael		Buxton	first.m.last@company.com	Michael.Buxton@company.com
5	Jerome	Be	Karlin	first_m_last@company.com	Jerome_B_Karlin@company.com
6	William	Jack	Rudolphsen	first_m_last@company.com	William_J_Rudolphsen@company.com
7	Mark	A	Wagner	flast@company.com	MWagner@company.com
8	Robert	M	Kral	lastf@company.com	KralR@company.com
9	William	L	Hubbs	fmlast@company.com	WLHubbs@company.com
10	Alan	J	Lacy	firstlast@company.com	AlanLacy@company.com
11	Jerome		Karlinsky	FLast>7@company.com	JKarlins@company.com
12	William		Rudolphsen	FLast>8@company.com	WRudolphs@company.com
Sheet1

 

[alinka]

Re: Complicated IF formula (how do I combine 11 if statement

You can make the button as I explained earlier with form-tool-box or as Zack explained

"You can also assign the macro to drawings or Auto Shapes for some creative latitude. Hope this helps."

You assign the macro by clicking the Auto Shape ( a circle or so ) with your mouse right button.

Hi, thanks for ur help! I am just new to this - and was not sure...

 

[alinka]

Re: Complicated IF formula (how do I combine 11 if statement

ZACK,

Another thing I just noticed: the two formulas you're using for syntax "first.m.last" and "first_m_last" are wrong:

Case "first.m.last@company.com"
                Range("G" & i).Formula = "=A" & i & "&"".""&LEFT(B" & i & ",1)&"".""&C" & i & "&""@""&F" & i
            Case "first_m_last@company.com"
                Range("G" & i).Formula = "=A" & i & "&""_""&LEFT(B" & i & ",1)&""_""&C" & i & "&""@""&F" & i

They give a wrong email address if they don't have a middle name (double period, or double underscore)

In my latest spreadsheet, I used the new formulas:

for first.m.last =A4&"."&(IF(B4="","",(LEFT(B4,1))&"."))&C4&"@"&F4

for first_m_last =A4&"_"&(IF(B4="","",(LEFT(B4,1))&"_"))&C4&"@"&F4


But I don't know how to rewrite these into the VB code. Thanks so much!

 

[Zack Barresse]

Okay, this should do it. Replace this with the first part...


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> emailAddy4()

    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Lrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    Lrow = Range("D65536").End(xlUp).Row

    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rng <SPAN style="color:#00007F">In</SPAN> Range("G2:G" & Lrow)
        i = rng.Row
        a = Range("E" & i).Value
        b = a & "@company.com"
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> b</FONT>


Start with the Sub emailAddy4, and end with the Select Case ...

Just replace the top portion. If you need the code in it's entirety posted let me know. It doesn't take into account column F, this is because it will always be changing, unless everybody on the email list uses the same ISP/email. But it doesn't use column D as requested.

And the 'i' is just a variable. Variables need Dim'ed, or Dimensions set to them. So we're claiming i as Long (usually we'd use Integer, but it could go longer than 32500 {around there approximately} up to the last row - 65536). So we set i to be the row number that will increment in the For Next Loop. The Select Case method is just a fancy If/Then statement with lots of options.

As for buttons, if you right click an empty space on your toolbar you can select either Forms or Control Toolbox. Depending on the uses of your objects (command buttons, checkboxes, radio buttons, list boxes, scroll bars, etc) is going to dictate which toolbar you use. The Control toolbar controls use ActiveX and need the code placed 'within themselves'. A Forms button on the other hand, you can Assign a Macro to it. These are great and very useful and probably the best to use for beginners. They look almost exactly like Control Toolbox controls, so be careful.

From the Forms toolbar select the Button icon, then click anywhere on your sheet to place it. A pop-up box will come up with a list of your macros (thoughtfully entitled Assign Macro). Choose the macro you want to assign to it (e.g. emailAddy4) and click OK. Now everytime you hit that button the emailAddy4 (or other assigned macro) will run.

This particular macro can run over the top of the cells, meaning you can run it over and over again even if you just ran it. How's that for long winded?! Hope it helps. Take care! :D

 

[Zack Barresse]

Okay, here is the code with your formulas (revised), sorry, I didn't see that until after I posted. Hope this works.


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> emailAddy4()

    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Lrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    Lrow = Range("D65536").End(xlUp).Row

    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rng <SPAN style="color:#00007F">In</SPAN> Range("G2:G" & Lrow)
        i = rng.Row
        a = Range("E" & i).Value
        b = a & "@company.com"
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> b
            <SPAN style="color:#00007F">Case</SPAN> "first.last@company.com"
                Range("G" & i).Formula = "=A" & i & "&"".""&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "first_last@company.com"
                Range("G" & i).Formula = "=A" & i & "&""_""&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "first.m.last@company.com"
                Range("G" & i).Formula = "=A" & i & "&"".""&(IF(B" & i & "="""","""",(LEFT(B" & i & ",1))&"".""))&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "first_m_last@company.com"
                Range("G" & i).Formula = "=A" & i & "&""_""&(IF(B" & i & "="""","""",(LEFT(B" & i & ",1))&""_""))&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "flast@company.com"
                Range("G" & i).Formula = "=LEFT(A" & i & ",1)&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "lastf@company.com"
                Range("G" & i).Formula = "=LEFT(C" & i & ",1)&A" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "fmlast@company.com"
                Range("G" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(B" & i & ",1)&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "firstlast@company.com"
                Range("G" & i).Formula = "=A" & i & "&C" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "lastfirst@company.com"
                Range("G" & i).Formula = "=C" & i & "&A" & i & "&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "FLast>7@company.com"
                Range("G" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(C" & i & ",7)&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> "FLast>8@company.com"
                Range("G" & i).Formula = "=LEFT(A" & i & ",1)&LEFT(C" & i & ",8)&""@""&F" & i
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
                Range("G" & i).Formula = "No Set Syntax"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        
        rng.Value = rng.Value
        <SPAN style="color:#00007F">If</SPAN> rng = "" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
            ActiveSheet.Hyperlinks.Add Anchor:=rng, Address:= _
                "mailto:" & rng, TextToDisplay:=rng.Text
    <SPAN style="color:#00007F">Next</SPAN> rng

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>


Let me know how it goes!

 

[alinka]

Re: Complicated IF formula (how do I combine 11 if statement

Zack,

You rock! Everything works perfect, except for the "lastf" syntax. Your code makes lfirst instead....Here's your code:

Case "lastf@company.com"
                Range("G" & i).Formula = "=LEFT(C" & i & ",1)&A" & i & "&""@""&F" & i

I tried to rewrite it into:

Range("G" & i).Formula = "=C" & i & "&LEFT(A" & i & ",1)" & i & "&""@""&F" & i

but I'm getting a Run-Time error as something is wrong with my syntax. Plz help me fix this one and I'm all set!!! Thanks.

On another note, can u recommend a good source for me to learn how to recreate regular excel formulas into vb codes? Thx!

 

[Zack Barresse]

Ok, sorry, replace it with this code:


<font face=Courier New>            <SPAN style="color:#00007F">Case</SPAN> "lastf@company.com"
                Range("G" & i).Formula = "=C" & i & "&LEFT(A" & i & ",1)&""@""&F" & i</FONT>


And as far as translating Excel formulas into code formulas, I'm not sure. I did it myself, didn't read it in a book. Just purusing around these forums, asking questions, dabling a little more and a little more every time. The big parts, anything inside quotes in a formula must have double quotes in VBA. Bill Jelen & Tracy Syrstad's new book is supposed to be good though. Don't remember the title, but it JUST came out. There's also 1600 VBA Examples I've heard is good. I don't have any of these books, so it's all hearsay to me. But I trust the people on the board.

Hope this helped.

 

[alinka]

Re: Complicated IF formula (how do I combine 11 if statement

wow, this is great! Thank you - everything works like it should. I am very very grateful.

I just created a macro button that creates email addresses for ALL rows -- thanks to your help.

Now, I decided that I should probably have a button that does all that we did but row by row. Is it a major change to the code?

I know u previously did it row by row - and i asked for all rows. I just decided I need row by row too because in case I do manual changes to my G column - I can't overwrite it anymore with the current code. Sorry for bugging you so much.

 

[Zack Barresse]

What do you mean 'row by row'? Do you mean by one cell at a time? Kind of like the first macro did? Click a cell that you want it done to, click a button, 'poof' you've got the email addy? Take me through the steps. (nice pic btw )

 

[alinka]

What do you mean 'row by row'? Do you mean by one cell at a time? Kind of like the first macro did? Click a cell that you want it done to, click a button, 'poof' you've got the email addy? Take me through the steps. (nice pic btw )

Yup! exactly -- you got it right. Thx so much! ray:

This icon pic suckkkkks bug glad u like it

 

[Ekim]

Re: Complicated IF formula (how do I combine 11 if statement

Ekim, I posted your code into a module -- and get this error: Compile Error: Invalid Outside Procedure.

As noted in my post, my macro is base on the data and layout shown in your first post. The macro works perfectly on that data and that layout. There are no “Compile Errors”.

Since posting the macro, the layout had changed. Consequentially, my macro will not produce expected results, although I note that some of the principles in that macro have been adopted in Zack’s final macro.

Now you are saying that you don’t want to read from column D ……….

Mike