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>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
634
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,277
Members
430,536
Latest member
Manoj Gaidhankar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top