Populating a cell with a formula based on drop down "action"

[kanderna]

I have a sheet where the user makes a selection from a drop-down menu. A cell (where I currently have a VLOOKUP), then auto-populates with a rate based on what they selected in the drop-down. All works fine and good, but here's where my question comes in. The rate that is autopopulated is a starting point. I want to user to be able to tweak the rate up or down as they see fit. The problem with that is once they tweak, the VLOOKUP formula is gone.

My question is... is there a way to replace the formula (or at least the value behind the VLOOKUP) whenever a new selection is made from the drop-down (and perhaps when they open the file)?

Excel 2013. Thanks!

 

[kanderna]

I think I got it. This seemed to do what I want:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B9")) Is Nothing Then
Range("B10").Select
ActiveCell.Formula = "=VLOOKUP(B2,D5:I9,MATCH(B9,D4:I4,0),FALSE)"
End If

End Sub