Adding value of two cells to get result in 3rd cell

[Anshu]

I want to add the value of two cell to get output in 3rd cell with the help of VBA but the following code do not work.

Sub add()
Range("C2").Value = Range("A2").Value + Range("B2").Value
End Sub

I've already posted the question here https://excel.tips.net/T003217_Using...n_a_Macro.html
and come to know that the VBA is correct.

So the sample file is attached here.

[DocAElstein]

this is your worksheet

_____ Workbook: Book1.xlsm ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1
2
3		5			2
4

Worksheet: Sheet1

This is your macro, as given by you in your uploaded file. It is incorrect. It will not work.

Sub add()
Range("C8").Value = Range("B3").Value + Range("E2").Value
End Sub

This next macro below works fine for me. This is the correct macro for your worksheet

Sub add()
Range("C8").Value = Range("B3").Value + Range("E3").Value
End Sub

[Anshu]

Well, then there may be some fault by my side which I'm unable to find out.
I'm using latest build of MS Office Pro Plus 2019. I should check the code with older version. It may help.
Thank you!!

[DocAElstein]

Did you realise and understand that your macro was incorrect? The issue was that you were using an incorrect macro. The macro was incorrect. Your formula was incorrect. The macro in your uploaded workbook was incorrect. So it did not work for me for that reason.
Your macro was using "E2" which was incorrect. You should have been using "E3"
In your uploded workbook, your data was in row 3 , so you must use B3 and E3. Your macro was using B3 and E2. That was the problem.

[Anshu]

Yes Sir! I've realised the mistake immediately after seeing your code. And I'm thankful that the mistake was highlighted properly with red color to bring to my notice.
But, actually, that was typed E2 instead of E3 just by mistake. The real problem is something else. Because even after correcting the code, it did not work for me.
As shown in the image, the cell C8 remain empty always for me.
Annotation 2020-08-26 205837.jpg

Before posting my problem here and tips.net, I've searched a lot and try many codes, but nothing works. One of the code, I tried was-

Sub Addition()

Dim Add As Integer
Dim var1 As Integer
Dim var2 As Integer
var1 = Range("B3").Value
var2 = Range("E3").Value
Add = var1 + var2
Range("C8").Select
ActiveCell.FormulaR1C1 = Add

End Sub


But it, too, failed to work for me.
2nd.jpg


Then only I reached to the conclusion that the problem may be somewhere else!

[DocAElstein]

Try running this macro once, then try your macros again, there is a small chance that it might help

Sub Oops()
 Let Application.ScreenUpdating = True
 Let Application.Calculation = xlCalculationAutomatic
 Let Application.EnableEvents = True
 Let Application.DisplayAlerts = True
End Sub

Note also that your macro must be in the same workbook and in the worksheet object code module of the worksheet in which you have the data to be added.

[Anshu]

Yes..it works! Thank you!

But there is a problem in the code

Sub add()
Range("C8").Value = Range("B3").Value + Range("E3").Value
End Sub

In order to get the result, I must run the code every time manually.

What I want is, Real time update, means...
as soon as I enter a value in the cell B3 or E3, the value should be updated in the cell C8 automatically and immediately, without running the code manually.

[DocAElstein]

The macro will need to be an Event macro, like the ones we have done previously

[Anshu]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("C8").Value = Range("B3").Value + Range("E3").Value
End Sub

This works fine.

Thank you for the support and guidance!

[DocAElstein]

That macro is OK, if it works for you. It will recalculate everytime you select any cell anywhere in the worksheet. So it is a little inefficient, The macro will be run many times when it is not needed to run.

This might be a bit better,

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$3" Or Target.Address = "$E$3" Then
  Let Application.EnableEvents = False
  Let Range("C8").Value = Range("B3").Value + Range("E3").Value
  Let Application.EnableEvents = True
 Else
 
 End If
 
End Sub

But you can use the one you prefer