Using a column in If Else statement

**Izeath** · 03-09-2010, 03:10 PM

How am i be able to do this. If im going to use Mod on B3 and there is a statement saying,
if z = 0 then, it will use the D3; if z = 1 it will use E3; if z = 2 it will use F3 and so on. z is equals to the Mod equivalent on B3.
The Mod will be only 8.

I' am just new in Excel VBA..

**zbor** · 03-09-2010, 03:13 PM

What is z?

**Izeath** · 03-09-2010, 03:30 PM

z is the equivalent of the number that is being Mod. Mod(B3,8) = z

**zbor** · 03-09-2010, 03:35 PM

Is this what you looking for (in B3):

=INDEX($D3:$K3,1,MOD(CODE(A3),8))

However, MOD(84,8) is 4... so why is 205 in red instead of 147?

**Izeath** · 03-09-2010, 09:34 PM

Well it is just an example.. If z = 0 it will use D3 as a value.. does this INDEX command work on If Else statements? and how to construct that in a VBA Module?

**DonkeyOte** · 03-10-2010, 04:09 AM

@zbor: given MOD 0 is D should your formula not in fact be:

=INDEX($D3:$K3,1+MOD($B3,8))
(ie result for row 3 would in fact be 89 rather than 147)

@Izeath - what is your utlimate goal - ie what are you using the above calculation for exactly ?

**zbor** · 03-10-2010, 04:20 AM

True, true

**Izeath** · 03-10-2010, 09:04 AM

I was just wondering if how to make the INDEX command in a VBA Module. But the command you guys posted here is just what i need.

Thanks zbor and DonkeyOte

**Izeath** · 03-12-2010, 05:34 AM

Another question, is there a certain function that instead of getting 89 in the =INDEX($D3:$K3,1+MOD($B3,8)), it will look for another value below 89? Like if z = 1 it will choose the value 221? it will go down 1 cell? or if z = 2 it will go down 2 cells?

**DonkeyOte** · 03-12-2010, 06:46 AM

Change the INDEX range and then perhaps (if I've understood)

=INDEX($D3:$K$2108,1+MOD($B3,8),1+MOD($B3,8))

you could also use OFFSET, however, OFFSET is a Volatile function - may / may not be a concern to you

**Izeath** · 03-13-2010, 10:55 AM

=INDEX($D3:$K$2108,1+MOD($B3,8),1+MOD($B3,8)) I tried to use 255 instead of 8 but it turns out to be #REF! How can i fix it?

**DonkeyOte** · 03-13-2010, 05:42 PM

The range D:K has only 8 columns - a column index_num outside of that range (1-8) will result in a #REF error.

**Izeath** · 03-13-2010, 10:10 PM

i got 8 columns. when i choose a column it doesn't end there but it would choose a row below it. so after picking a value at $D3:$K3, e.g. it chose $J3, then it would choose a value from 0-255 ranging from $J3:$J252. i hope you understand.