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.

 

[Andrew Poulsom]

Why can't you have the user enter the numbers in a couple of cells near column L? There are plenty of cells on a worksheet and using those cells in your formula would provide an audit trail.

 

[mumps]

Maybe:

Sub Test()
    Dim num1 As String
    Dim num2 As String
    num1 = InputBox("What is your labor cost?")
    num2 = InputBox("What is your productivity rate?")
    ActiveCell = num1 * (num2 * Cells(ActiveCell.Row, "H")) / Cells(ActiveCell.Row, "H")
End Sub

 

[excel6000]

I am copying data from another spreadsheet and pasting it into my spreadsheet, so basically columns F to O need to match the spreadsheet which I'm copying from. If I were to add cells in either direction, the info would be very much out of place.

 

[Andrew Poulsom]

What's wrong with using columns P and Q?

 

[excel6000]

Thank mumps, that worked great. Is there any way to make it so the macro just closes when you hit "cancel", currently it gives me the Run-time error '13': Type mismatch error.

Andrew, it's difficult to explain without seeing the spreadsheet. There is already a lot of data I don't want to clutter it with more columns. It also makes it more difficult to print. I understand you can hide columns and rows, but I'm already doing that in several areas.

 

[mumps]

Try this:

Sub Test()
    Dim num1 As String
    Dim num2 As String
    num1 = InputBox("What is your labor cost?")
    If num1 = "" Then Exit Sub
    num2 = InputBox("What is your productivity rate?")
    If num2 = "" Then Exit Sub
    ActiveCell = num1 * (num2 * Cells(ActiveCell.Row, "H")) / Cells(ActiveCell.Row, "H")
End Sub

Keep in mind that if you hit cancel, no calculations will be done.

 

[excel6000]

Awesome! Thank you mumps. Worked perfect!!

 

[mumps]

My pleasure.

 

[mikerickson]

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

This formula results in NUM1*NUM2, no matter is in H10.