Cycle through loop and amend cell contents

[JAGIUS]

Hi ALL,

I need a VBA macro which will look through a dynamic range (will chnage from month to month) copy a reference number, search for it in another sheet and then chnage the cell contents of another column once that refewrence is found in the other sheet. I then need it to come back to the original sheet and loop throug the next reference number and do the same as mention above.

So in the below the macro starts in cell C3 copies the reference, looks in the 'Instrument' sheet in cell A3 on another sheet, finds the reference in the instument sheet and makes the amendment to the cell as specified in E3. Then it returns to the sheet with the list below and moves down to C4 and so on untill it reaches the last cell.

	A	B	C	D	E
2​	Sheet	Ref column	Reference	Column to be Amended	Amedment
3​	Instrument	INSTRMNT_ID	002613-ODSB-EUR-016	DT_INCPTN	20151228​
4​	Instrument	INSTRMNT_ID	002613-ODSB-USD-013	DT_INCPTN	20151228​
5​	Instrument	INSTRMNT_ID	017627-SUCL-SGD-019	DT_INCPTN	20220919​
6​	Instrument	INSTRMNT_ID	028116-SUCL-EUR-010	DT_INCPTN	20200326​
7​	Instrument	INSTRMNT_ID	028116-SUCL-EUR-021	DT_INCPTN	20200228​
8​	Instrument	INSTRMNT_ID	047229-ODAN-EUR-016	DT_INCPTN	20130101​
9​	Protection Received	PRTCTN_ID	048401-CCFA-USD-007	ORGNL_PRTCTN_VL	28126.95426​
10​	Protection Received	PRTCTN_ID	048725-CCFA-USD-008	ORGNL_PRTCTN_VL	205180.0000​

I have no idea how to start this.....


Any ideas wold be very much appriciated

 

[Herakles]

Can you have a formula in column E to look up the required value?

 

[JAGIUS]

Can you have a formula in column E to look up the required value?

The value is based on the reference in column C. I guess essentially a VLOOKUP but once the value is found it has to be updated with that of column E

 

[Herakles]

Does the value in column E always depend on the value in column C?

Is so, then, by having a formula it will be dynamic.

 

[JAGIUS]

Does the value in column E always depend on the value in column C?

Is so, then, by having a formula it will be dynamic.

yes beacuse without the reference you will not know what row the chnage has to be applied to. This is to save the user having to do the chnages manually each month untill the chnages are fixed at source. As mentioned, they will be added to each month

 

[JAGIUS]

Anyone