PDA

View Full Version : Solved: Help with VBA code



Ger
02-05-2008, 12:42 AM
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
02-05-2008, 01:41 AM
=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
02-05-2008, 05:06 AM
Can you tell me more. in which cell must i copy this formula?

Bob Phillips
02-05-2008, 05:19 AM
B16, then across and down

Ger
02-05-2008, 05:26 AM
I get an error on A$16,N

Ger

Bob Phillips
02-05-2008, 05:33 AM
Should probably be

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

for you

Ger
02-05-2008, 05:49 AM
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
02-05-2008, 05:52 AM
Can you post an example of this Ger?

Bob Phillips
02-05-2008, 05:53 AM
BTW, I still think it is RIJ, not RIJEN, should be ROW not ROWS.

Ger
02-05-2008, 06:05 AM
see B41 till B45. cell B41 must be a 7 B43 a 11

Ger

Bob Phillips
02-05-2008, 06:29 AM
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
02-05-2008, 06:46 AM
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
02-05-2008, 07:12 AM
Ah, the infamous Maastricht.