Code works with numbers only, not with words

[MarcoAUA]

Hi you'll

PLease see below thread for the "solution" (not really) to my problem.
http://www.excelforum.com/excel-prog...ther-cell.html
I short, I am building a wb for teachers to keep track of grades. It has 2 sheets: Data sheet with info from students and the subjects, and Grades sheet, were the teacher fills out the grades.
In grades, the student are listed from left to right and the subjects from top to bottom. To keep a good view and make it good to navigate to the seperate students/subjects I have found some macros that when value is selected from a drop down (form control) a specific cell or entire row is selected.

Please Login or Register  to view this content.

A very smart person came up with the following formula =OFFSET(Data!A16,V1,0).
In V1 a number is generated according to the sequence from the students name. These names come from Data A17:A51.
When Data A17 is named John, V1 in Grades becomes 1, when Data A18, is named Paul, Grades V1 becomes 2, etc.
Since the wb is still under construction, the "students" are called 1:35 and the macro works great (it selects the cell in Grades with this number). When I change the numbers to names, nothing happens!
The drop down is positioned on A5.

Anybody has ideas how to solve this? That the macro also works with names and not only with numbers?
Any help is really appreciated,
WB attached.
MarcoAUA.

[Leith Ross]

Hello MarcoAUA,

I am not sure where the students are located you are referring to since you don't mean the worksheet name. However, your macro for selecting students can be shortened by using an algorithmic approach.

Please Login or Register  to view this content.

[rylo]

Hi

From a quick look, Grades!W1 contains the name. When you run selectstudent, change the V1 to W1 and this will set findrange to be the cell in row 5. You can then do a findrange.select to do the same thing that you are doing with the case statement.

HTH

rylo

[MarcoAUA]

Hi Leith & Rylo,

Thanks for the replies.
I copied Leith's suggestion and the situation stayed the same. When I imlemented rylo's. It worked.

I changed

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

and everything worked great.
But when i wanted to change the other V1 to W1, it debugged: Run-Time error 13. Type Mismatch.

I just like to know why. Any ideas?

Anyway, I'am going to fool around with it a bit and see if it stay like this when I built the wb.

THNX a Lot,

MarcoAUA.

[MarcoAUA]

I am trying to copy this, not in a row but down a column.
I have changed

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

I am pretty sure I need to change something here

Please Login or Register  to view this content.

.
I think in

Please Login or Register  to view this content.

since this makes it look in the rows.
How can I change this so that it looks in column(2)?

Any help is welcome

Please Login or Register  to view this content.

Marco

[rylo]

Hi

What is it you are trying to achieve? Give a specific example and what you want returned.

rylo

[MarcoAUA]

I have created another drop down. Simular set up.
When value selected, this time the subjects (which are listed underneath each other) need to be selected. Actually, the row were the subjects are, needs to be selected.
I tried to alter the macro you provided

Please Login or Register  to view this content.

What this does is, it selects row20 (14 rows below range) no matter which value is selected. I like to reach to the point that when a value is selected from the drop down (which takes the values from Data!A5:A14), the entire row in which the value resides is selected (the range mentioned is copied the Grades!B6, B20, B34...). these are the rows 6, 20, 34, etc.

Any idea how to accomplish this?

Marco.

[rylo]

Marco

If you just want to select the entire row that has the nominated subject, then it is

Please Login or Register  to view this content.

So if you select c from the drop down in A7, it will put 3 in M1, which will give you a c in N1. The code

Please Login or Register  to view this content.

will set findrange to be B34 so the above will select row 34.

Is that what you are chasing?

rylo

[MarcoAUA]

Hi Rylo,

This is precisely what I had in mind!!
Here the final code

Please Login or Register  to view this content.

For the ones interested, I have attached the WB. Not done yet, but getting closer.

Lot's of THNX to Rylo and the rest.

Marco.

BTW, according to me, this is solved