Use macro to paste new info into a cell location found by another formula

[Dayne]

Howdy I have a parts list that I have built a search page for. I need the parts list locked out accept for the Qty needs changed from the search page. I can find it, display it, display the location and hyperlink to it. I can also of course calculate the new amount but I need it to change the amount. I can make a macro that will paste info into a know location but the location will change with the search so I need it to paste to the location found in a cell or find the location and then paste it. Please can you help.

search results	Qty
Part# 1234	4	Removing	1	Remove from	$E$6
		new Qty	3
Parts list
Part# 1234		Current Qty	4

<colgroup><col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3141;"> <col width="28" style="width: 21pt; mso-width-source: userset; mso-width-alt: 977;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2699;"> <col width="14" style="width: 11pt; mso-width-source: userset; mso-width-alt: 488;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3095;"> <col width="36" style="width: 27pt; mso-width-source: userset; mso-width-alt: 1256;"> <tbody> </tbody>

 

[Trevor_S]

I'm assuming that these are two separate worksheets, Search and PartsList. Cell D2 of the Search sheet tells you that 1 needs to be removed, and cell F2 of the Search sheet tells you that it needs to be removed from the existing value of $E$6 of the PartsList sheet.

Search sheet:

	A	B	C	D	E	F
1	search results	QTY
2	Part# 1234	4	Removing	1	Remove from	$E$6

<tbody>
</tbody>

PartsList sheet:

​

	A	B	C	D	E	F
6	Part# 1234​			Current Qty​ <strike></strike>	4
7

<tbody>
</tbody>

​

If so, try this:

Sheets("PartsList").Range(Sheets("Search").Range("F2").Value).Value = Sheets("PartsList").Range(Sheets("Search").Range("F2").Value).Value - Sheets("Search").Range("D2").Value
​

This will deduct the value of Search sheet cell D2 from the value of whichever cell on the PartsList sheet is contained in cell F2 of the Search sheet.


​

 

[Dayne]

Thank you I think I was entering something wrong with the exact code I was trying to copy from you because it was saying script out of range but when I used the Sheets("PartsList").Range(Sheets("Search").Range("D3").Value).Value= out of the code to paste to the location in that cell it worked perfect