Solved: Help with VBA code

[Ger]

I have a problem. I use excell 2003.
I used a if statement to give a range of cells a value. But i need more than 8 if statements.

I got a range of value (for example B1 to j13) In cells B16 to .. must appear a value (1 to 10) depending on the value in B1 to J13.

See example

[Bob Phillips]

=14-MATCH($A16,N(OFFSET(B$1,14-ROW($1:$13)-1,0)),-1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[Ger]

Can you tell me more. in which cell must i copy this formula?

[Bob Phillips]

B16, then across and down

[Ger]

I get an error on A$16,N

Ger

[Bob Phillips]

Should probably be

14-VERGELIJKEN($A16;N(VERSCHUIVING(B$1;14-RIJ($1:$13)-1;0));-1)

for you

[Ger]

Thanks, it works. I translated rows in rijen and not rij.
Just 1 little problem. The value in row 13 is sometimes < row 12. If this happens now i get wrong numbers. What to do about the value #NB .

Ger

[Bob Phillips]

Can you post an example of this Ger?

[Bob Phillips]

BTW, I still think it is RIJ, not RIJEN, should be ROW not ROWS.

[Ger]

see B41 till B45. cell B41 must be a 7 B43 a 11

Ger

[Bob Phillips]

Try this array formula

=IF(MIN(IF($A16<=B$1:B$13,ROW(B$1:B$13)))=0,"",MIN(IF($A16<=B$1:B$13,ROW(B$1:B$13))))

=ALS(MIN(IF($A16<=B$1:B$13;RIJ(B$1:B$13)))=0;"";MIN(IF($A16<=B$1:B$13;RIJ(B$1:B$13))))

and definitely RIJ not RIJEN, we want an array of row numbers not a count.

[Ger]

Your right. The translation of row = rij. But i was wrong in tranlating it to rijen.

Your new formula works perfect.

Thanks a lot.


greetz from Holland (maastricht)

Ger

[Bob Phillips]

Ah, the infamous Maastricht.