Divide numbers in 2 different cells and give the answer automatically in a third cell using VBA

[TimothySmith]

Hello All,

Thank you in advance for any help you can provide me to point me in the right direction.

I have Hires (Cell D13) and Submissions (Cell D7), and I need to divide hires from submissions. The third cell (D16) is the Conversion Rate from the other two cells. I have searched google to solve this problem but only come up with deleting cells or splitting cells. Because this Excel DB is for the company I work for I need to have it Automated in VBA so nobody accidentally deletes a formula. I would like to have the conversion rate populate as the user enters the numbers into those two fields however, it can be done by button as well. 

Again, thank you for any assistance you can provide me. You are much appreciated for what you do to help others. 

[#2]

instead of using VBA why not just lock the sheet to protect those cells from being changed (i.e. unlock any cells that the use will need to enter data into)

[#3]

I work remote (my office is a little over 200 miles away) so there will be another administrator for the database until I move closer to HQ. That administrator doesn't know anything about excel except to add raw data. I don't want the individual to somehow screw things up if I give him the password to unprotect the Sheet and I don't want to try emailing the DB since it will have PII. By putting it in VBA I don't have to worry about him or anyone else changing anything then I have to try to figure out what happened.

[#4]

@TimothySmith Don't know why you would need to give them the PW to unlock the sheet.  You set the cells they need to 'touch' as unlocked and then all the formula cells get locked.  If they have a problem they will have to have you fix it (which would be the same if there is a problem with the VBA).

If you still insist on VBA then add a _change routine onto the VBA sheet you want to automatically update.  So in VBA (Alt-F11) you need to double click the sheet icon to open the macro page:

Then paste something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Me
        .Range("D16").Value2 = .Range("D7") / .Range("D13")
    End With
End Sub

you could get fancy and check what was updated and only make the change if one of the input cell changed and you could also do warnings or something if they try to enter a value into D16 ...  But based on your request this is the 'answer' even though I still think locking the sheet is a much better solution.  Just right click on cell D7, select Format Cells, go to the Protection tab and unclick 'Locked'.  Repeat with D13 and any other cells/ranges you want them to actually enter data into.  Then lock the sheet and you can even select options if you want them to be able to select those locked cell (but not change) or not even select, etc...

Either way, good luck.

[#5]

Thank you mtarler, I really do appreciate the help and may wind up doing what you suggested with protecting the cells.

Thanks again, you are appreciated