Copying a value from a cell in a sheet, then plugging it into a calculator, then pasting the returned values into another cell.

AlexCombs

New Member
Joined
Apr 30, 2021
Messages
2
Hi, I'm brand new here, a former coworker recommended this forum to me. I have tried to poke around and see if I can find anyone having the same issue but I haven't found anything quite like I'm wanting to do.

I have 2 sheets in a workbook, on Sheet 1; I paste in product data including a market price, Sheet 2 is a calculator that is very complex and clunky and I am kind of embarrassed of it so I blacked it out. Basically I can input a value into one cell then like 6 other formulas and values work together and it returns me a Lo, Mid, and Hi buy price for the product.

What I want to do is create a macro that will take the Market values from column D in Sheet 1, plug them into cell C3 in sheet 2 and then copy the values from E3, E4, and E5 back into sheet 1 into the H, I, and J columns which I have labeled to display which value I want where, Also I want to be able to add more products to Sheet 1 in the future so basically what I would like it to do is be able to run based off of cell position, so if it is on cell D2735, it will know to paste the values into H,I, and J2735. Also just a quality of life thing Id like it to do it for all of cells at once or for me to hold down Ctrl + Shift + X and it just goes down the list.


I have highlighted all of the aforementioned cells and included a couple screenshots. If anyone could help me with this I would be truly grateful, macros and vba are a bit out of my depth.
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    30.7 KB · Views: 4
  • Capture2.PNG
    Capture2.PNG
    7.8 KB · Views: 4

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,391
Try:
VBA Code:
Sub CalculateValues()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, calcWS As Worksheet, MP As Range
    Set srcWS = Sheets("Sheet1")
    Set calcWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each MP In srcWS.Range("D2:D" & LastRow)
        calcWS.Range("C3") = MP.Value
        Range("H" & MP.Row).Resize(, 3).Value = Application.Transpose(calcWS.Range("E3:E5").Value)
    Next MP
    Application.ScreenUpdating = True
End Sub
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,701
Messages
5,654,815
Members
418,155
Latest member
demasisi

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