Hi,
I am new in Excel. Thanks in advance for your valuable time.
I am trying to calculate Similar type of user data in a excel sheet.

Batch Name Amount Total
---------- ------ ----
Acct1 15.2 95.2
Acct1 60
Acct1 20
Batch2 40 40
Batch3 80 100
Batch3 20
Batch4 33.5 100.5
Batch4 44.5
Batch4 22.5
Accounts Rec 89.56 114.89
Accounts Rec 25.33

How could I show programmatically (Macro/VBA) the total amount of all the similar Batch name(Acct1,Batch2, Batch3..etc)
in third column?
Like 15.2+60+20 = 95.2 is showing in the third column.

Batch name could be different then this name.

Here is my thought:--
I think we need to read each character of the cell until we got the same name in next cell then we have to add (Amount) and write total amount in last column.
Any thought and help will be greatly appreciated.

You can use the Excel function SUMIF. Go to Excel Help for explanation of the function. The function in your case would be in the third column

=SUMIF(Range in first column of all batch names, specific batch name, Range in second column of all the amounts)

Just Enter the function in the cells you currently have the totals.

I appreciate your feedback.
This work great but in order to do that I need to copy/paste or type the formula in each cell I want.

Is there way I can do programmatically using vba or macro?

I am doing something like now…
=SUMIF(B2:B52,B2:B52,J2:J52)
=SUMIF(B2:B52,B2:B52,J2:J52)
=SUMIF(B7:B57,B7:B57,J7:J57)
….etc

The following VBA code will do was you want:

Sub BatchTotal()

    Dim ws As Worksheet
    Set ws = Sheets("Batch Total") 'Worksheet's name with data

    Dim Bcell As Range
    Dim BN As String
    Dim BNcell As Range
    Dim BatchTotal As Double

    Set Bcell = ws.Range("A5") 'First cell with Batch Name


    Do Until IsEmpty(Bcell.Value) 'Batch name range continuous data

        If Bcell.Value <> Bcell.Offset(-1, 0).Value Then
            Set BNcell = Bcell
            Do Until BNcell.Value <> Bcell.Value
                BatchTotal = BatchTotal + BNcell.Offset(0, 1).Value
                Set BNcell = BNcell.Offset(1, 0)
            Loop
            Bcell.Offset(0, 2).Value = BatchTotal
            BatchTotal = 0
        End If

        Set Bcell = Bcell.Offset(1, 0)

    Loop

End Sub

Open a module and paste in the code. Change the name of the worksheet
and the cell address for Bcell to the first cell with the Batch Names. Note the Batch Names must be a continous listing or the first do loop will stop at the first blank cell.

Awesome FullSquat ;)
That works great.
I have one more question.

what do I have to do if I want to calculate another column in same worksheet. Can I use same function? How? Thanks

Batch Name Amount1 Total1 Amount2 total2
---------- ------ ---- =----- ----
Acct1 15.2 95.2 30 60
Acct1 60 20
Acct1 20 10
Batch2 40 40 10 10
Batch3 80 100 25 45
Batch3 20 20
Batch4 33.5 100.5
Batch4 44.5
Batch4 22.5
Accounts Rec 89.56 114.89
Accounts Rec 25.33

The following should work for the two Totals case.

Sub Batch2Totals()

    Dim ws As Worksheet
    Set ws = Sheets("Batch Total 2") 'Worksheet's name with data

    Dim Bcell As Range
    Dim BN As String
    Dim BNcell As Range
    Dim BatchTotal1 As Double
    Dim BatchTotal2 As Double


    Set Bcell = ws.Range("A5") 'First cell with Batch Name


    Do Until IsEmpty(Bcell.Value) 'Batch name range continuous data

        If Bcell.Value <> Bcell.Offset(-1, 0).Value Then
            Set BNcell = Bcell
            Do Until BNcell.Value <> Bcell.Value
                 BatchTotal1 = BatchTotal1 + BNcell.Offset(0, 1).Value 'Total1
                 BatchTotal2 = BatchTotal2 + BNcell.Offset(0, 3).Value 'Total2
                Set BNcell = BNcell.Offset(1, 0)
            Loop
            Bcell.Offset(0, 2).Value = BatchTotal1
            BatchTotal1 = 0
            Bcell.Offset(0, 4).Value = BatchTotal2
            BatchTotal2 = 0
        End If

        Set Bcell = Bcell.Offset(1, 0)
    Loop


End Sub

I just used two variables for the running totals and set the offset property for the BNcell to the columns with the amounts for the respective totals.

woww that works great. Awesome.

I have probably one last question to ask..
1) I want user to force enter data(Date) in this column (Cell-) ACC-DATE) like exact same format.

ACC_DATE BATCH_NAME Amount1 total1 total2
-------- --------- ------- ------ -----
02-MAY-07
03-JUN-07
10-JUL-07
20-AUG-07

2) User can not enter data into next cell/row until they enter date.
Kind of message box type will be a great to do I guess.

(Date can not be null and other then dd/MON/YY format.)
Thanks for your valuable time.


**Is there way can I lock the total1 and total2 column using macro/VBA?
Thanks