Complecate Macro to search value of intersect Rows and Columns with conditions

[hassan khansa]

Dear All : i have sheet contain in Column A Number of Exams and in the first Row the Name of Students ( more 50 students name ) and in the second Row the name of Classes and the Range ( B3:GG) is Marks of Student. and in Column Q i have the Number of some Exams and in cell R2 the Student Name .

what i need a macro Littel Complicated , that search each Number of Exams in Column Q with Column A at the same time search the Name of Student that exist in Cell R2 with Row 1 , then find the first and Second and third intersect of these searches and put the result in Column S and U , also find the the Classes of the Marks that found and put the result in Column R and T.

please see attached file.

Thanks in Advance

[fredrs05]

Is there a reason you have to use VBA?

The approach below would work just as well.

I used named ranges.

Please Login or Register  to view this content.

[watersev]

hi hassan khansa, VB option, press Run button

[hassan khansa]

dear Mr. Watersev , this code exactly what i need , but made some change to my workbook and didn't know how to edit the code to run as well as .
so please help me to edit the code for the changes below :
1- i separate the sheet for two sheets , sheet1 the data of No. Exams and Students Name and Marks , the sheet2 is the Result begin from Column T that is the Exam No. and the name of student is A5 ( not R2 ) .
2- maybe A5 = "" ( Name of student is Blank ) , i don't want to get Error or Debug , i want the macro do nothing .
3- i have more than 50 names of student in first row in sheet1 , not just three names ( George , Elie , Melissa, Maya , Charbel , Toni , Hani , Youssef ...... Ext ) and sheet2 A5 maybe one of these 50 names or Blank .
4- if the Exam No in sheet2 Column T exist in sheet1 Column A , than i want the macro to search for the first greater and get the result of it .

please see attached file , sheet 1 and sheet 2 , to see what changed .

very thanks for your help Mt. Watersev.

[hassan khansa]

Dear Mr Watersev. may you help me in with my issue above and Edit your code to get the result hat i want ?.

Thanks in advance

[watersev]

please check attachment

[hassan khansa]

Dear Mr. Warersev , i'm very thankfull , this code exactly what i want and it works perfect .

Many thanks Boss.

[hassan khansa]

Dear Mr. Watersev ,
i know that i'm bothering you with me , but i still have 1 problem when i change the Exams No. in sheet1 Column A ( as you will see in attached file ) it gives me another Result than i want, i think the code search the Value in sheet2 column T with the Rows No. of sheet1 column A . what i want from this macro is to Match the Value of sheet2 column T with the Value of sheet1 column A ( as lookup or Match ... ).
i tried to edit the macro by myself but i failed . sorry for bothering you Mr. Watersev.
please see attched file to see the difference between the Result of Macro and the Result what i need .

i'm Hopefull that you will Help me .

Thanks in advance

[watersev]

The code provided was written basing on the following assumptions:
1. Exam numbers come sorted in ascending order
2. Exam number will be unique without duplicates

This example file comes unsorted. I've added sorting to the code. If sorting is not acceptable and data should stay as it is, please shout.
If to delete duplicate number 15 in A18 (which to my understanding should not be there) you will get expected result now.

[hassan khansa]

Dear Mr. Watersev , this is the code what i want , it works very very perfect .

Thanks for your help Very Very much.

[hassan khansa]

Dear Mr Watersev : the Macro Run perfect in the tested sheet i sent to you . but when i copy the Macro to the original Sheet and press Run , it gives me ( Run-time error '9': Subscript out of Range ) ,and fill Yellow the Line : Loop Until data(1, strtcol) <> stname , in the Macro .
why this happen with me in the original sheet ?
please waiting your reply

[watersev]

hi, change this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[hassan khansa]

Sorry Watersev to late to reply you , i was sick ,
i changed the line above as you told me and it works good , but i faced another Debug on the line ( .TextToColumns DataType:=xlDelimited, other:=True, otherchar:="|" ) and colored yellow and gives me this error : Run-time error '1004' : No data was selected to parse.

[watersev]

try this, new lines are underlined:

Please Login or Register  to view this content.

[hassan khansa]

Dear Mr Watersev , the macro works perfect .
thanks a lot man