Input Box to Create and Input Info into a Formula Which Then Populates a Cell with a Value

[excel6000]

I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)

Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:

=(NUM1*(NUM2*$H10))/$H10

So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.

 

[excel6000]

Yes, that is correct... the H10 values cancel each other out. But in some cases on my spreadsheet, I had to change the $H10 value on the top of the fraction. So I actually ended up adding another NUM3 which replaced the "$H10" which gets multiplied by NUM2, but the bottom of the fraction is still $H10. So my formula now says:

=(NUM1*(NUM2*NUM3))/$H10

 

[mikerickson]

You could do this all with Named values.
Create Names aNUM1, aNUM2, aNUM3, use them in formulas and (when needed) either change the values of the Name in the Name Manager or use VBA routines to change the value of the Names.
(I discover that NUM1 is an illegal name, because it might be a cell reference)

e.g. in the define Name box
Name: aNUM1 RefersTo: =1

To change via Macro

Sub test()
    Dim uiValue As Double
    Dim strName As String, namedValue As Double
    
    strName = "aNUM1"
    namedValue = Evaluate(ThisWorkbook.Names(strName).RefersTo)
    
    uiValue = Application.InputBox("Enter the Value for aNum1", Default:=namedValue, Type:=1)
    If uiValue = 0 Then Exit Sub: Rem canceled
    ThisWorkbook.Names.Add Name:="aNUM1", RefersTo:=uiValue
End Sub

Changing the value of a Name would remove the need to track down (or remember) all the cells with formulas that need that value.