Using vba to match a term and copy a figure when not next to each other

[Mr_Bill]

I am trying to get a script that will look at a certain cell for a term. In column A the cell would be in A8,A12,A16,A20 and so every 4 rows. The value to copy would be in Column D in cells D6,D10,D14,D18 every 4 rows. Then around row A200:E200 there would be a key and this key would have the Terms to match to. If the term matched it would copy the value from the cells and paste into the matching term column in the key. The yellow highlighted areas are needed for other things and there to show startrows etc so it will fit seamless into the real book. Hoping this will be an easy one, can this if it needs to be done as active worksheet instead of its sheetname. Thank you for looking

Screenshot of Diagram
KM4EdxF.png

Workbook with Diagram
weighted sample.xlsm

[Richard Buttrey]

Hi,

Does the attached help? I've put the key on sheet2, you can delete the other one.

[Mr_Bill]

Richard is it possible to have them on the same sheet and filling down? I dont mean to sound picky or ungrateful its these sheets are created using a form. since I am no good with vba would be too complex to create the extra sheets and some how change the Sheet2.Range("Keylist") to the new sheet name . Its easy for me to make more if they work with the activesheet (not sure if that is its name the sheet that is open and being worked in). If that made any sense. Also noticed when the grade is change it doesnt update the key.

[Richard Buttrey]

Hi,

Which are the grade values. Your original post only seemed to be asking that the column D values should change whenever column A fruits change and that a new fruit be added to the table. Or at least that's what I interpreted it to mean.

Please explain exactly how you use this. i.e. which cells do you change/add and what cells should change when this happens.

[Mr_Bill]

I will try and do this here column wont so much change as new one will be added. Only column D will be edited. Column A for the code should only be matching the headers in the key and inserting the correct grade into the key. I have a form for the page itself which I can input data like this

Assignment name| Blank | Date | Grade Received | Grade Possible
Due Date
Assignment Type


When I input this information sometime I will have a grade received and sometimes I wont so if no grade is entered it shows - as filler and I can come back later and change that. It would be the only figure I would edit by hand the rest is by a form. Only the grades received would be changed and that is the number put in the key from column D

Assignment types should always be five of them so for example

KEY
Portfolio | Essays | Research | Final | Records Then each of then assignment categories are assigned a weight like this
22% | 20% | 30% | 18% | 10% These figures are used in a formula after you average all the rows to get the weighted average in the course.

Sorry I caused confusion with the fruit was just using it as filler. hopefully this clears it up