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

[AlexCombs]

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.

 

[mumps]

Try:

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

 

[AlexCombs]

@mumps
You are now my favorite person, thank you so much, that worked like a dream!

 

[mumps]

You are very welcome.