Require VBA Code for copy the calculation result to one column synchronously

[Sakurayuki]

Step 1
After both A2 and B2 are entered, the calculation result of cell C2 = A2*B2 , Copy C2 result to E2
Example
A2 cell key in 5 , B2 cell key in 6
So C2 cell get the result 30
PS : A2 and B2 must be completely entered before C2 can be calculated
At this time, copy the result of C2 cell to E2 cell

Step 2
Example
A2 cell key in 2 , B2 cell key in 9
So C2 cell get the result 18
PS : A2 and B2 must be completely entered before C2 can be calculated
At this time, copy the result of C2 cell to E3 cell

[Kaper]

To copy the cell content you would need VBA. My suggestion is to use event handler for Change event in Sheet2:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("A2:B2")) Is Nothing Then
    If Len(Range("A2")) * Len(Range("B2")) > 0 Then 'both col A and col B has something inside
      If IsNumeric(Range("A2")) And IsNumeric(Range("B2")) Then
        Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Value = Range("A2") * Range("B2")
      Else
        MsgBox "Both A2 and B2 has to be numbers", vbCritical
      End If
    End If
End If
End Sub

But to have it working properlu in case you enter a whole new pair of values, you would need to clear the A2:B2 content before.
Otherwise you enter new A2 and oldB2*newA2 will be calculated (this result is not needed) and then when you enter also new B2 proper result newB2*newA2 is stored in first available cell in column E.



But probably it would be better to write these pairs to be multiplied in consequtive rows. Then the code would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Columns("A:B")) Is Nothing Then
  For Each rng In Intersect(Target, Columns("A:B"))
    Cells(rng.Row, 3).ClearContents
    Cells(rng.Row, 5).ClearContents
    If Len(Cells(rng.Row, 1)) * Len(Cells(rng.Row, 2)) > 0 Then 'both col A and col B has something inside
      If IsNumeric(Cells(rng.Row, 1)) And IsNumeric(Cells(rng.Row, 2)) Then
        Cells(rng.Row, 3).Formula = "=" & Cells(rng.Row, 1).Address(False, False) & "*" & Cells(rng.Row, 2).Address(False, False)
        Cells(rng.Row, 5).Value = Cells(rng.Row, 1) * Cells(rng.Row, 2)
      Else
        MsgBox "Both Columns A and B has to be numbers", vbCritical
      End If
    End If
  Next rng
End If
End Sub

See attached file (macros execution have to be enabled).

[Sakurayuki]

First of all, thank you first, the first code is what I thought, but there is still a small problem. A2 CELL wants to change the quantity, B2 CELL UNIT PRICE also wants to change the unit price, so that it can be recorded. May I ask the teacher where to change the code?

It means that both A2 and B2 must be changed at the same time to establish a record

The second method and the suggestion do not need to write code, E COLUMN becomes redundant, just use the original C COLUMN.

[jindon]

1) change the formula in C2 to =IF(COUNT(A2:B2)=2,B2*A2,"")
2) To a sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [a2:b2]) Is Nothing Then Exit Sub
    If WorksheetFunction.Count([a2:c2]) < 2 Then Exit Sub
    Application.EnableEvents = False
    Range("e" & Rows.Count).End(xlUp)(2) = [a2*b2]
    Application.EnableEvents = True
End Sub

[Sakurayuki]

Thank you, teacher

I think it still doesn't work. I can't handle it with a trigger event, because I changed one and recorded it, and the other has not been changed yet.
Change to a button to confirm the event

[jindon]

Do you mean only when B2 changed?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [b2]) Is Nothing Then Exit Sub
    If WorksheetFunction.Count([a2:c2]) < 2 Then Exit Sub
    Application.EnableEvents = False
    Range("e" & Rows.Count).End(xlUp)(2) = [a2*b2]
    Application.EnableEvents = True
End Sub

[Sakurayuki]

Do you mean only when B2 changed?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [b2]) Is Nothing Then Exit Sub
    If WorksheetFunction.Count([a2:c2]) < 2 Then Exit Sub
    Application.EnableEvents = False
    Range("e" & Rows.Count).End(xlUp)(2) = [a2*b2]
    Application.EnableEvents = True
End Sub

Awesome, that does the trick! Much appreciated! Thank you, Jindon teacher

At the same time, thank you Mr. KAPER teacher for your kind help