IF statements in VBA

[a8747]

Hello All

A while ago, you guys helped me solve an issue with sending mail from Excel. jaslake came up with a solution that has worked brilliantly since (below), but I want to improve the workbook that it sits in by removing a repetitive formula and putting its equivalent in VBA coding instead. Each row has a button that opens Outlook allowing you to send the email. The email contents are defined by what is written in the other cells on each row, hence the offsets etc below.

Please Login or Register  to view this content.

The Subject line

Please Login or Register  to view this content.

refers to a cell in the worksheet containing the following formula:

=UPPER("HELLO"&". "&IF(E2>"",E2,A2)&"."&" "&C2&"-"&D2&IF(F2="",". PROFIT:"&B2&"£.",IF(F2>0,"RUNNING:"&F2&"."))&" "&"PLS CALL THE OFFICE."&" EXO OPS")

...and it is this formula that I want to remove from the sheet and put into VBA form, and I'm struggling. I think I can either have the code in a new macro, or I can have the code written in the existing procedure, but I just can't get it to work.

If you guys could help out, it would be brilliant!

cheers

Adam

[snb]

Probably this suffices:

Please Login or Register  to view this content.

[a8747]

Thanks snb, that's shortened the macro, but I'm looking to write a vba-code 'IF' statement to replace the in-cell formula. Problem is, is that the formula above is repeated over and over on each row, about 150-rows.
Replacing 150 formulas with a single vba code version should reduce the size of the file, I just can't write it!

[snb]

check if this is what you want.

Please Login or Register  to view this content.

[a8747]

That works very well, thanks! What I am doing at the moment is replacing the absolute cell references with offset references to allow the single piece of code to manage each of the 150 buttons, so this is what I am using:

Please Login or Register  to view this content.

In the above Ive so far just replaced the first reference [E2] with the range.offset bit...

...but it doesn't like it, so I'm wondering what I'm doing wrong?

thx

***EDIT: Looks like I've managed to solve it myself with

Please Login or Register  to view this content.

Just had to get rid of the square brackets.
Many, many thanks for your help snb!!!!