Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 30th, 2007, 04:57 PM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Cell computation using vba/Macro

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.


  #2 (permalink)  
Old July 31st, 2007, 09:16 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.




  #3 (permalink)  
Old July 31st, 2007, 01:26 PM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default




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


  #4 (permalink)  
Old August 1st, 2007, 11:30 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.



  #5 (permalink)  
Old August 1st, 2007, 04:34 PM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #6 (permalink)  
Old August 1st, 2007, 05:28 PM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.








  #7 (permalink)  
Old August 2nd, 2007, 10:38 AM
Authorized User
 
Join Date: Jul 2007
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro help for the VBA impaired Christen Beginning VB 6 2 July 17th, 2006 02:09 PM
speed up macro Excel VBA crmpicco Excel VBA 8 December 21st, 2005 11:17 AM
stop macro from running on EVERY cell change!! forkliftpete Beginning VB 6 1 October 26th, 2005 07:36 AM
Cell returns its own formula after a macro! timoma Excel VBA 2 December 19th, 2004 03:48 PM
Hide VBA code when forwarding macro sh333384 Excel VBA 7 September 2nd, 2004 10:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.