# EXCEL – How to have a formula output and user input on the same cell

microsoft excelvbaworksheet-function

I would like help from someone as I believe this problem requires VB. I basically have a CELL(A4) where people will be entering values to find out the corresponding calculations displayed below.

However I want to add an additional function where popular values are automatically entered into that same cell(A4).

Cells E7 and E8 will receive input and multiply it by 960(E7) or 765(E8) and transfer that value to cell(A4) so the rest of the formulas update properly.

Basically have the user input the bags directly in A4 or enter the number of containers in E7 or E8.

**This is where i have the problem because when I enter a formula on A4 it gets deleted once you enter a value directly on it.

**Also note that the result from E7 and E8 should be displayed (in A4) as I need it as well.

My sheet:

You can use a worksheet change function in vba. Something like

Private Sub Worksheet_Change(ByVal target As Range)

If Intersect(target, Range("e7:e8")) Is Nothing Then Exit sub

Application.enableevents = False
ActiveSheet.range("A4").formula="=if(e7="""",e8*765,e7*960)"
Application.enableevents = True

End Sub


Anytime the sheet changes it checks if the cell changed was e7 or e8. If it was it puts the formula

=if(e7="",e8*765,e7*960)


in cell A4. If you then put a value in cell A4 it will stay until you again change e7 or e8. You may need to adjust to get it to do exactly what you want but the idea is there.

This would go in the worksheet object under microsoft excel objects in vba.

EDIT:

New code, this one will only do something if the cell has a value added or changed, it won't run if you delete a value.

Then it checks if e7 was edited. If it was e8 is deleted. If it wasn't we know e8 was edited so e7 gets deleted. Formula stays the same.

Private Sub Worksheet_Change(ByVal target As Range)

If Intersect(target, Range("e7:e8")) Is Nothing Or target.Value = "" Then Exit Sub
Application.EnableEvents = False

ActiveSheet.Range("A4").Formula = "=if(e7="""",e8*765,e7*960)"

If Intersect(target, Range("e7")) Is Nothing Then
ActiveSheet.Range("e7").ClearContents
Else
ActiveSheet.Range("e8").ClearContents
End If

Application.EnableEvents = True

End Sub