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.
[TABLE="width: 252"]
<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>[TR]
[TD="width: 90, bgcolor: transparent"]search results[/TD]
[TD="width: 28, bgcolor: transparent"]Qty[/TD]
[TD="width: 77, bgcolor: transparent"][/TD]
[TD="width: 14, bgcolor: transparent"][/TD]
[TD="width: 89, bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Part# 1234[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Removing[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]Remove from[/TD]
[TD="width: 36, bgcolor: transparent"]$E$6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]new Qty[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]Parts list[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 36, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Part# 1234[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Current Qty[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

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
Back
Top