Creating a VBA Function

**ColdDeath** · 08-04-2013, 07:55 AM

Hi,

I'm trying to write my own excel funtion with VBA here. Currently I'm trying to replace this code (it's dutch):

Please Login or Register  to view this content.

It works, but I've stumbled upon an error... I really don't want to break this code in a million peaces to find out what the problem is...

So, I decided to write a function that does exactly the same, but looks 10 times better. The only problem is, yesterday I found this site that did exactly what I was looking for, now I lost it.
I do know a few things when it comes to programming, I just don't know anything when it comes to excel programming...

So far I have:

Please Login or Register  to view this content.

So now it can do the same as the function I've written with the if function. Now I want to active it by typing:

Please Login or Register  to view this content.

Kind regards,
ColdDeath

**Norie** · 08-04-2013, 08:06 AM

Before jumping to VBA you should try replacing all the IFs in that formula with something like VLOOKUP or INDEX/MATCH.

Perhaps something like this.

Formula:

Please Login or Register  to view this content.

PS That's Scottish.

**ColdDeath** · 08-04-2013, 08:30 AM

Don't know what I'm doing wrong. I've rewritten the code in Dutch:

Please Login or Register  to view this content.

Problem is, it doesn't check if target cell E4 is equal to one of the cells in M4:M36. It just writes down list L4:L36 in that order and ends with a list of zero's.

What does index do?
What does match do?

IFERROR is the opposite of IF I recon?
So let's say I write:

Please Login or Register  to view this content.

IFERROR does the opposite of IF, so in this case IF would write 1 and IFERROR would write 2. Correct?

**Norie** · 08-04-2013, 08:32 AM

You are missing the 0 in the MATCH/GELIJK function, which is actually a mistake I made when originally writing the formula.

**ColdDeath** · 08-04-2013, 08:36 AM

=ALS.FOUT( INDEX( $L$4:$L$36; VERGELIJKEN( E82;$M$4:$M$36;0 )); "")

It works! Thank you!

**Norie** · 08-04-2013, 08:43 AM

IFERROR is not the opposite of IF.

IFERROR is used to return a value if an expression evaluates to an error.

=IFERROR(<expression>, <valueiferror>)

Try this which is how it would have been done pre-IFERROR.

Formula:

Please Login or Register  to view this content.

I've also made the lookup ranges absolute which you'll need if you are going to copy this formula down from row 4.

**oeldere** · 08-04-2013, 08:48 AM

@Colddeath

For future use it's easier as you post an excel file on the forum.

Then you don't have to translate your formula from Dutch into English.

Forummembers can add the solution and post it, and after you downloaded it, you get the formula translated in Dutch in your file. (excel will do that for you).

Then there is also no problem with a ; (semicolon) and a , (colon).

your translation of als.fout should be iferror (instead of IF.Errror) => there is no dot in the english formula.

Creating a VBA Function

LinkBack

Thread Tools

Rate This Thread

Display

Creating a VBA Function

Re: Creating a VBA Function

Re: Creating a VBA Function

Re: Creating a VBA Function

Re: Creating a VBA Function

Re: Creating a VBA Function

Re: Creating a VBA Function

Thread Information

Users Browsing this Thread

Similar Threads

Help on creating a if function or a nested if function????

Creating a function to simplify a more complex function

Help creating a function

[SOLVED] Creating a Macros function but co9ntent of function only works in a sub plz help

Creating a Function

Bookmarks

Bookmarks

Posting Permissions