# Nested If

• Hi

I am new to VBA in excel. In Excel you can only have up to 7 nested if statements, I need to have more than that, how do I do this in VBA in Excel AND how do I execute the VBA code in excel afterwards? I know how to open VBA in Excel and insert a new module but from there on I need help. The code is very simple, basicly if the cell (A10) is 0 it needs to take the value of A9, but if A9 is zero it needs to take the value of A8.

Help will be apreciated.

• Re: Nested If

Can you attach a small example file so we can see what values & how many potential IFs ?

Usually, this sort of thing can be solved without the need for VBA - there are alternatives to IF statements that will give you the result you want.

• Re: Nested If

=MAX((A1:A10<>0)*(ROW(A1:A10)))

which is an array formula, so commit with Ctrl-Shift-Enter.

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Nested If

Hi

I attached my example, please see Cell B48. Thanks for all the replies!

## Files

• Re: Nested If

Sorry I attached the wrong file! The If statement must start from D43 on upwards until d8

• Re: Nested If

Hi Wilna

I have changed B48.

Take a look and see if you can use this.

Alring

## Files

• Re: Nested If

Try this

=OFFSET(D7,COUNTA(D7:D43)-1,0)

• Re: Nested If

With this formula, you can have the desired result:

Code
``=INDIRECT(ADDRESS(MAX(((D8:D43<>0)*(ROW(D8:D43))*(ISNUMBER(D8:D43)))),4))``

Put it in cell B48. Conclude not with enter, but with Ctrl-Shft-Enter.

The solution by Alring is wrong, ik looks for a maximum, not the last cell in a range.

The solution by Will R does not take into count empty cells.

However, it's up to you to choose which one you like most, Wilna.

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Nested If

Hi Wigi

As fare as I can see then is it then highest number that Wilna want to have. And If i understand you code too then is it also the highest number you get.

But let us see which one Wilna want to use.

Alring

• Re: Nested If

Alring,

your formula gives the maximum, WillR's and mine give the value (not necessarily the maximum) of the range D8:D43. But when the values are non-decreasing, our outcomes will be identical

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Nested If

Hi Wigi

That's right.

• Re: Nested If

Hi Everybody

Thanx for all the help! All the formulas will give the desired result! The desired result will be most probably be the maximum every time except if the user puts in the wrong data. But thanx again I will be able to finish from here on. Have a nice day!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!