VBA to overwrite cell value using INDEX MATCH and INDIRECT

[Richard JIBS]

Hello,

I would like some help with some VBA to overwrite a cell.

In the first worksheet, I have a list of vocabulary covered in Term 1 (worksheet called "Term1"), with a difficulty rating assigned to each word. There will be other worksheets called, eg "Term2" and "Term3".

Word	Translation	Difficulty
orange	orange	3
strawberry	fraise	1
apple	pomme	3
banana	banane	2

<tbody>
</tbody>

In the second worksheet, I have developed a test where basically a word appears from the vocab list of a particular term, eg Term 1. The more difficult words appear more often. The values that appear in the test are: word, translation, difficulty rating and the name of the worksheet the word has been picked from.

In this test worksheet, I would like a cell where I have the option of overwriting the difficulty value in the vocab list in "Term1". For example, if the word "apple" comes up and I see it's listed as difficulty rating 3, I would enter the new rating, 1, in a cell in this worksheet. This action would trigger a macro which would carry out an INDEX MATCH-type lookup of the word "apple" in the worksheet "Term1" using INDIRECT and overwrite in "Term1" column C the value 3 changing it to 1 for the word "apple".

Select words from worksheet:	Term1
Word:	pomme
Translation:	apple
Difficulty:	3
Select new difficulty?:

<tbody>
</tbody>

I don't know where to start with the VBA macro, so any help is appreciated!

Thanks,

Richard

 

[Richard JIBS]

To follow this up, a bit more info below.

If in cell B7 of the test worksheet I entered the value 1 for 'Select new difficulty?', the worksheet "Term1" would then look like this:

Word	Translation	Difficulty
orange	orange	3
strawberry	fraise	1
apple	pomme	1
banana	banane	2

<tbody>
</tbody>

Thinking about it a bit more, the INDEX MATCH function isn't quite right; the purpose is more to find the Difficulty cell address of the answer (in this case C4 in "Term1") for this to be the cell that gets changed.

Any help much appreciated.