Allow for User Input but keep a formula if no input

[Nivian]

Good day

Need help with VBA. If cell is blank a formula must be active in the cell, so for example A1 is blank however if I put a value in cell A2 then an activate formular like =A2*10 should be in A1 so I get result. However if i want to type my own value in A1 I can without affecting the formula should I need to.

 

[Nivian]

Good day Snakechips

Thank you so much for the help. I combined some of your code with another and came up with this. It works great. Let me know if you have any resources for me to learn from would really appreciate it.


Private Sub Worksheet_Change(ByVal Target As Range)
Macro1 Target
Macro2 Target
End Sub

Private Sub Macro1(ByVal Target As Range)
If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
If Not Target.Cells.Count = 14 And Not Target.Cells.Count = 1 Then Exit Sub

Application.EnableEvents = False

If Range("D27") = "" Then Range("D27").Formula = "=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"

Application.EnableEvents = True
End Sub


Private Sub Macro2(ByVal Target As Range)
If Intersect(Target, Range("D29")) Is Nothing Then Exit Sub
If Not Target.Cells.Count = 14 And Not Target.Cells.Count = 1 Then Exit Sub

Application.EnableEvents = False

If Range("D29") = "" Then Range("D29").Formula = "=INDEX(ASSETDB[Asset],MATCH(D27,ASSETDB[Inventory number],0))"

Application.EnableEvents = True
End Sub

@Nivian
You can only have one instance of each type of Worksheet Event code in any one sheet.
So, if you want multiple/optional actions they all need to be handled by the logic within your code.

It's partially guesswork on my part but I imagine that something like the below is what you are wanting?
Rather than put a 'hard' formula in the cell that is complementary to the changed (Target) cell, I have used the vba Evaluate function to generate the value.
Replace the previous code with this and see how you get on.

Private Sub Worksheet_Change(ByVal Target As Range)
'If changed cell does not intersect with cells of interest then do nothing
If Intersect(Target, Range("D27, D29")) Is Nothing Then Exit Sub
'If more cells selected than represents a cell of interest then do nothing
If Not Target.Cells.Count = 14 And Not Target.Cells.Count = 1 Then Exit Sub
'assume selection is row 27
r = 27
' check and change r if  rowis 29
If Intersect(Target, Range("D27")) Is Nothing Then r = 29
'Disable the triggering of Events so that changes made by this code don't create an infinite loop of this code
Application.EnableEvents = False

'do appropriate lookup for the complimentary value

    If r = 27 Then
        Range("D29").Value = WorksheetFunction.IfError(Evaluate("=INDEX(ASSETDB[Asset],MATCH(D27,ASSETDB[Inventory number],0))"), "Not Found").   '??? Edit formulas to suit
        Else
        Range("D27").Value = WorksheetFunction.IfError(Evaluate("=INDEX(ASSETDB[Inventory number],MATCH(D29,ASSETDB[Asset],0))"), "Not Found").  '????
   
    End If
   
'***  Enable the triggering of Event Code
Application.EnableEvents = True

End Sub