VBA for formula to remain in cell even if overwritten

[lpratt]

i was hoping for some help in creating a VBA.

Example:
B13 has a product code and D13 has a vlookup in it to look for names of a product based on B13
if this doesn't return a result I want to be able to type in a name in D13 but keep the formula so that if B13 is changed the vlookup in D13 will still work.

is this possible

 

[offthelip]

Put this code in the code for the worksheet:

Public Formul As String

Private Sub Worksheet_Activate()
Formul = Range("D13").Formula
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B13")) Is Nothing Then
  Application.EnableEvents = False
  Range("D13").Formula = Formul
  Application.EnableEvents = True
End If
End Sub

You need to activate the worksheet to initalise the Formul variable so you might want to either put the same code in the workhseet open event or select a different workhseet as the one the workhseet opens on so that you always trigger the worksheet activate event before you use the workhseet.

 

[kevin9999]

A cell cannot contain both a formula and a constant. If you type in a constant, the formula is gone. If you use VBA Worksheet_Change event code to put the formula back if the cell contents change - then the constant is gone.

 

[offthelip]

A cell cannot contain both a formula and a constant. If you type in a constant, the formula is gone. If you use VBA Worksheet_Change event code to put the formula back if the cell contents change - then the constant is gone.

Read the op carefully, if the vlookup in d13 fails to find a match the user types the name into d13. Then when b13 is changed the user wants the vlookup working again so the previous name is overwritten with the vlookup which now finds the next name, (or not). It toggles between a constant and a formula

 

[kevin9999]

@offthelip yes, you're right, I stand corrected.