VBA Solution

[sambit]

Dear Sir,
please help to resolve the issue by VBA.

pl find example file

Invoice No Receive Qty Sale Qty Balance Qty
(Receive Qty - Sale Qty)
1 10 2 3
1 2
1 3
2 25 10 5
2 2
2 5
2 3
3 40 15 2
3 16
3 7
3 50 20 16
3 14

 

[Hareesh_KLD]

use following code

Sub CalcBalanceQty()
    Dim i As Integer
    Dim c As Integer
    Dim rq As Integer
    Dim sq As Integer
    Dim bq As Integer
 
    Range("F2").Select
 
    For c = 1 To WorksheetFunction.CountA(Range("F2", Range("F" & Rows.Count).End(xlUp))) - 1
        If c <> WorksheetFunction.CountA(Range("F2", Range("F" & Rows.Count).End(xlUp))) - 1 Then
            ActiveCell.End(xlDown).Select
            rq = ActiveCell.Value
         
            For i = 1 To (Range(ActiveCell, ActiveCell.End(xlDown)).Rows.Count) - 1   
                    sq = sq + ActiveCell.Offset(i - 1, 1).Value
            Next i
         
            bq = rq - sq
            ActiveCell.Offset(0, 2).Value = bq
         
            rq = 0
            sq = 0
            bq = 0
        Else
            ActiveCell.End(xlDown).Select
            rq = ActiveCell.Value
            sq = WorksheetFunction.Sum(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 1).End(xlDown))
         
            bq = rq - sq
     
            ActiveCell.Offset(0, 2).Value = bq
        End If
    Next c
 
   Range("A1").Select
End Sub

Regards,
Hareesh

 

[vletm]

sambit
You asked to solve 'something' by VBA - okay,
but next ... hmm ...
Why did You asked to 'use following code' ... (Use Code-tags)
if then there were some challenges?
If 'use following code' then ... the result would be same ... hmm?
> I did one new code ... test to press 'Balance Qty' >
Is that something You're looking?

 

[Hareesh_KLD]

'use following code' - Just a formal thing

don't know how to insert code tags, not able to understand the tip available
My code as per sambit requirement based on attached sheet

 

[vletm]

Hareesh_KLD
Have You read Posting a Small Block of VBA Code in a Post from:
https://chandoo.org/forum/threads/posting-a-sample-workbook.451/#post-73705

Oh ... that was Your code ... sorry!
I skipped those ... lines.
Use Code-tags!
Anyway, my code is there too for sambit.

 

[Marc L]

Hi !

vletm, your code seems to not produce expected result …

sambit, why a VBA code ? 'cause just with easy formulas …

Hareesh, same result as yours but with fewer codelines & variables
and without Select (as a good code does not need any !) :​

Sub Demo1()
     Dim Rg As Range, L&
          L = [E2].End(xlDown)(2).Row
     Set Rg = Cells(L, 6).End(xlUp)
Do While Rg.Row > 2
    Rg(1, 3).Value = Rg.Value - Application.Sum(Rg(1, 2).Resize(L - Rg.Row))
     L = Rg.Row
     Set Rg = Rg.End(xlUp)
Loop
     Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[vletm]

Marc L
... was example-files result correct or what?
or
do he just need same values?
and
'please help to resolve the issue by VBA.'

 

[Marc L]

Yes in fact initial presentation is so poor ‼
As the issue was not ever explained neither the need !
The reason why I didn't answer anything after first read
as per forum rules initial presentation must be crystal clear …

As within sample attachment last column title is highlighted,
we can suppose the expected result is this last column
but maybe Hareesh and I took a wrong way,
so why I wrote your code « seems » …
My post was first to show Hareesh another way to get same result.

Better is to avoid a VBA code and use Excel inner features like formulas.
​

 

[sambit]

Dear All,
Thanks to all for resolve the issue. I have consider for VBA Solution because in my work book data is more than 2,00,000 line item. if i used formula it take more time to calculate.

Lot of Thanks to Vletm, Marc L and Hareesh_KLD.