1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007 Macro Sum 2 Columns

Discussion in 'Business Applications' started by harrista, Feb 23, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. harrista

    harrista Thread Starter

    Joined:
    Feb 23, 2011
    Messages:
    5
    I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

    Sub Test()
    On Error GoTo CancelExit:
    With Selection
    .Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
    .Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
    End With
    CancelExit:
    On Error GoTo 0
    End Sub

    I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

    Sub Test()
    On Error GoTo CancelExit:
    With Selection
    .Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
    .Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
    End With
    CancelExit:
    On Error GoTo 0
    End Sub

    Sale Date Sale Price Adj Sale Price 2010 Land 2010 Imp 2010 Total
    7/7/2009 10,000 10,000 13,300 0 13,300
    7/7/2009 35,000 35,000 34,700 0 34,700
    6/7/2007 97,500 95,100 95,600 0 95,600
    10/9/2009 32,000 32,000 23,800 0 23,800
    4/27/2009 50,000 50,000 62,600 0 62,600
    9/7/2007 5,000 4,900 6,200 0 6,200
    3/12/2007 23,000 22,400 27,700 0 27,700
    5/4/2007 15,000 14,600 14,000 0 14,000
    7/19/2007 3,500 3,400 2,800 0 2,800
    10/3/2007 15,000 14,700 15,000 0 15,000
    5/1/2009 30,000 30,000 33,800 0 33,800

    I would be summing the Adj Sale Price and the 2010 Total everytime. There is data above and below each "mini-table" and all are of different rows. I tried to set it up to highlight the first column series as all the rows in that series will alway be the same number of rows.

    All help is greatly appreciated!!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, I think a sampel of your sheet will simply matters.
    Please attach one and we'll take a look
     
  3. harrista

    harrista Thread Starter

    Joined:
    Feb 23, 2011
    Messages:
    5
    Thank You.... I have uploaded excerpts of actual data that I would need to automate with a macro or vba. All the data would always be in this format.

    Thanks You!
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Okay, I downloaded the file and will take a look at it during the weekend
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I got deeper into your sheet and think I got it straight now.
    I added an empty row between the main table and the 'mini table' and think I got the calculations right now.

    Let me know, Okay?
     

    Attached Files:

  6. harrista

    harrista Thread Starter

    Joined:
    Feb 23, 2011
    Messages:
    5
    Keebellah,

    Thanks... I am taking a look at it right now and will get back with you after I have a chance to absorb all coding.

    Thanks,

    harrista
     
  7. harrista

    harrista Thread Starter

    Joined:
    Feb 23, 2011
    Messages:
    5
    Keebellah,

    Thank you for your solution. I have added the missing formulas for Wtg Mean, PRD and have added a section to calculate the "Total AV / Sale Price" ratio. With that said.... I should let you know that this is a document that I have to submit to the State and as Government goes.... It has to be in their format to be compliant. I do believe that the blank line between the data and the calculations would be permitted. My problem comes in where the Townships were on seperate tabs. The tabs are reserved for Classes of properties... is.. Vacant Residential, Improved Residential, Vacant Commercial, Improved Commercial......

    There are more statistical tests that get performed on the data but it is based on the calculations and results that you have automated. As a thought..... If there is a way to select the "parcel_id" and use that as the defining range for that dataset, it would work great!!! Thoughts???

    I have attached the spreadsheet with my changes/additions to the code for the formulas..

    Thanks,

    harrista
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I could see what I can find for you.

    The idea of each township its own sheet is easier to work with, but you could use this to create your sheets and calculations and then have a macro that creates a new workbook with all the townships in one sheet with all the mini tables adn then the following township, etc.

    If this is an idea I can see if I can work it out for you.

    I'll check your posted update and let you know
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    It could be quite simple if you have a complete table with all the data in it to craete tables based upon a selected parcel id

    The question: does this mean that you want a tabel based on only 44-09-11-200-020.002-013 ?

    If so, you will need a possibility to select a parce_id and then create a table with all the data based on that choice only?

    I think I can build something that would work, and then delivere all the results with all the tables in one sheet with the mini table in between for submitting.

    Give this a thought and I will work it out with your last posted sample.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I've been takeing a look at the sheet and can probably come up with a simple solution:
    The idea is that you have two sheets containg data:
    Sheet 1 would contain at least 3 columns :

    Code:
    Type	                 Township           Twp._no.
    Vacant Residential – 	Johnson Twp.	6
    Vacant Residential – 	Newbury Twp.	9
    Vacant Residential – 	Milford Twp.	8
    
    Sheet2 will contain all the data for all the townships

    Code:
    parcel_id	                 twp. _no.	DLGF_no.	nghd_no.	class codeGrade	Condition         etc...
    44-10-26-100-030.012-010	6	44010	5100300	510           0	0
    44-10-26-100-050.003-010	6	44010	5100300	510           0	0
    44-10-17-300-002.001-010	6	44010	5100600	500           0	0
    44-10-06-300-002.001-010	6	44010	5106800	500           0	0
    
    Based upon both these sheets a sheet for each township can be created automatically based upon a selection made in Sheet1 and using the twp._no. as filter extract the data from sheet2 and place it in a new sheet named accordingly and then adding the 'mini table' under it.

    After that you can choose to cumulate all the created sheets into one new sheet that you can present to whatever office you need with all the townships, the tabels, the mini tabel and then below that the following township minitabel etc...

    Code wise it's quite simple.

    Let me know, maybe it's too much but I like coding :)
     
  11. harrista

    harrista Thread Starter

    Joined:
    Feb 23, 2011
    Messages:
    5
    Good Day Keebellah.... Thank you again for your post. You may have a solution! I would like to send you the complete file so you can see the entire package. it is approximately 5MB zipped. The file is too large to send via techguy.org . It may take a dual filter to get the information in a useable format and the second filter would probably be the "class code". If you think this would be acceptable, I will provide the breakdown of the class codes for you.

    Thanks,

    harrista
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi there,

    Well, I couldn't avoid it so I already did some coding.
    I'm attaching the last Excel sheet with just the two sheets.
    The DataRef sheet is protected (no password) just to show what I mean
    If you press the button you will be prompted for each sheet you want to create / update based upon the All-data contents.
    After that the Cumultavie sheet may be created

    I'll send you a PM with my email address if you wan to zip the complete file.
    I can't promise miracles or faster results since I do this after working hours, but I'll give it a shot.

    meanwhile here's my idea in practice.
     

    Attached Files:

  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/982411

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice