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

Dayne

New Member
Joined
Feb 27, 2019
Messages
2
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 resultsQty
Part# 12344Removing1Remove from$E$6
new Qty3
Parts list
Part# 1234Current Qty4
<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>
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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:

ABCDEF
1search resultsQTY
2Part# 12344Removing1Remove from$E$6

<tbody>
</tbody>


PartsList sheet:

ABCDEF
6
Part# 1234
Current Qty
<strike></strike>
4
7

<tbody>
</tbody>


If so, try this:

Rich (BB code):
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.


 
Last edited:

Dayne

New Member
Joined
Feb 27, 2019
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,430
Members
409,876
Latest member
Akash Yadav
Top