AUTO sum

[annadinesh]

Dear Sir,

i want to say that i have a sheet where i need to calculate the no of NOTE(Currency) received the whole day,

currently what i do is all the time i have to calculate the received amount with the amount entered in the cell.

what i need is i only enter the no of note on the cell and excel automatically add the no with the previous no of NOTE on that cell

like if the no of NOTEs in front of Rs. 1000.00 is already showing 10 and if i received 2 nos of Rs. 1000.00 NOTE then i need to enter ONLY 2 in the cell where already 10 was entered. after enterening 2 in that cell excel auto calculate the new entry with previous value and shows the value as 12 on that cell.

and also when i double click the cell the cxcel should shows =10+2

[TABLE="width: 251"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]500[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Please Do the Needful



Dinesh Saha
9932022569

 

[Learn Excel in Tamil]

Here is the sample coding,

Sub Autosum()

Dim A As Double
Dim B As Double
Dim C As Double
Dim D As Double


A = WorksheetFunction.Sum(Range("C2").Value, Range("J2").Value)
B = WorksheetFunction.Sum(Range("C3").Value, Range("J3").Value)
C = WorksheetFunction.Sum(Range("C4").Value, Range("J4").Value)
D = WorksheetFunction.Sum(Range("C5").Value, Range("J5").Value)

Range("C2").Value = A
Range("C3").Value = B
Range("C4").Value = C
Range("C5").Value = D

Range("D2").Value = WorksheetFunction.Product(Range("C2").Value, Range("B2").Value)
Range("D3").Value = WorksheetFunction.Product(Range("C3").Value, Range("B3").Value)
Range("D4").Value = WorksheetFunction.Product(Range("C2").Value, Range("B4").Value)
Range("D5").Value = WorksheetFunction.Product(Range("C2").Value, Range("B5").Value)

Range("J2").Value = Range("C2").Value
Range("J3").Value = Range("C3").Value
Range("J4").Value = Range("C4").Value
Range("J5").Value = Range("C5").Value

End Sub

 

[Learn Excel in Tamil]

Hi Dinesh,

You can download the link from following google drive : https://drive.google.com/file/d/1ppeIjd4BdC9MDklAnjqS84kx7_vcKALe/view?usp=drivesdk

 

[annadinesh]

Sir,

Thanks for the support,

But this did not help me

I want the calculation on same cell

like

if A1 Contains the Currency Detail
and on B1 I write the qty of the NOTES received and on C1 the Calculation should Show

A1 B1 C1
100 2 200.00

then if again i received 3 NOTES then I should Write like this ( i will write 3 on B1)

A1 B1 C1
100 3 200.00

then the result automatically should shows like this

A1 B1 C1
100 5 500.00

and if i double click on B1, then it should show as =2+3

pls check

or pls send your contact Number.


Regards
Dinesh Saha
9932022569

 

[Learn Excel in Tamil]

Hi Dinesh,

Can you confirm if 3rd time you received another 2 , 500Rs note, it should show


A1 B1 C1
100 7 500.00

and if i double click on B1, then it should show as =2+3+2 or = 5+2 (will be ok )?

 

[annadinesh]

Yes Sir,

Whenever i receive the no of NOTEs it should calculate with the last no.

if i double click on B1, then it should show as =2+3+2+1+3+4+3.... and so on


Regards
Dinesh Saha
9932022569

 

[Learn Excel in Tamil]

Okay will get back to you

 

[Learn Excel in Tamil]

Hi Dinesh,

I have saved the reveised file on same path with file name of Auto Sum_V1. you can download the same and let me know for any queries.

Or just copy this code and Replace below code into the Macro file

Sub Autosum()

Dim A As String
Dim B As String
Dim C As String
Dim D As String

Dim AQty As String
Dim BQty As String
Dim CQty As String
Dim DQty As String


A = Range("J2").Formula
B = Range("J3").Formula
C = Range("J4").Formula
D = Range("J5").Formula

AQty = Range("C2").Value
BQty = Range("C3").Value
CQty = Range("C4").Value
DQty = Range("C5").Value

If A = "" Then
Range("C2").Value = "=" & A & "+" & AQty
Else
Range("C2").Value = A & "+" & AQty
End If

If B = "" Then
Range("C3").Value = "=" & B & "+" & BQty
Else
Range("C3").Value = B & "+" & BQty
End If

If C = "" Then
Range("C4").Value = "=" & C & "+" & CQty
Else
Range("C4").Value = C & "+" & CQty
End If

If D = "" Then
Range("C5").Value = "=" & D & "+" & DQty
Else
Range("C5").Value = D & "+" & DQty
End If

Range("D2").Value = "=B2*C2"
Range("D3").Value = "=B3*C3"
Range("D4").Value = "=B4*C4"
Range("D5").Value = "=B5*C5"

Range("C2").Copy
Range("J2").PasteSpecial xlPasteFormulas

Range("C3").Copy
Range("J3").PasteSpecial xlPasteFormulas

Range("C4").Copy
Range("J4").PasteSpecial xlPasteFormulas

Range("C5").Copy
Range("J5").PasteSpecial xlPasteFormulas

End Sub

Thanks!
Learn excel in tamil

 

[annadinesh]

its not working as i wish

 

[Learn Excel in Tamil]

Can you try to download the file from the Drive