I have an Excel spreadsheet that I need help with a macro. I don't know much about VB scripting but I'm sure there must be a way to do what I want. Here is a link to an Angelfire site I created with pictures of my Excel spreadsheet, this seems a lot easier than trying to write it all out here. http://www.angelfire.com/tx6/excel/Excel.htm If anyone can help I would be very grateful! Thanks

HI Qbart I went to your side, and yes there are ways of doing this in a macro, or several macros, but this is almost better done in MS Access, a couple of queries and done deal. All you have to do is import the data in Access, (which is very easy sinceyou have in Excel) and then perform a few queries and done..... if you are not familiar with Access it might look like a hard task, but I think is far easier for what you need than coding a macro to do that same job in Xcel. Good luck If you decide to try it in accesss....post back...I can help further....but I might need a bit of the data ;-) later.......gis_tech

Thanks gis_tech, you have got me thinking. I am not that familiar with Access but I have messed with it for other things. Do you think there would be a way once I ran the queries in Access to copy the data back to Excel. I'm doing this for work and the final end user needs to have it in Excel format.

I have been looking at it too. I am on Excel97 and can not apply the 'Relative Reference' to my macros like I could with Excel2K. It continues to input the actual cell ranges. I can make my macro work but only for the data that is shown now so it would be no good for 2500 rows. I suspect that you also need to cross reference the Locations with the names or you have many different names. A pivot table was made for this type of data. Another fairly effortless way is to: 1. Click on column B (Price), Click Edit, Go to, Special, Blanks, Ok. Click on Edit, Delete, Entire Row, Ok. 2. Select A2 Input the formula =SUMIF('account',d2,'price') 3. Use Autofill on A2 to copy the formula down. (In summary: Delete blank rows, input the formula and copy it down) If you do need to xref with the Location then you would want to Concatenate Location and Account in column E and name it Account1 instead of column D. Change the formula to =sumif(account1',e2,'price') Then you could copy and paste special, values on column A and then delete column E or hide column E. HTH Bryan

Clickable link http://www.angelfire.com/tx6/excel/Excel.htm that opens in a new window. Bryan

Absolutely....once you run a query, update query or whatever in Access, the displayed table could be entirely selected and copied and pasted onto a regular excel spreadsheet. Looks to me that you are trying to summarize records (sum of $$$) per account in the spreadsheet, that in access would be as simple as summarizing doing a query, group by -- per account, and add for the price field use the "sum" in the query. That should yield a table that contains the total per account, this however in 1 record like this: total account ------ ------- $5,300 Fred --> just an example You can then assign this data (link it in Access ) to the original table in Excel, using the update command, based of course on teh account name.(if unique, that is) Hope It helps.....really takes longer to type this than to do these things.... :-) Wish I could help more....I could actually do it...<><>, but I know some people are quite sensititive with customer data, ( im one of them...) so I understand the issues... If more help, let me know, post back. GIS_tech STEPS: 1. IMPORT DATA IN ACCESSS 2. PERFORM A 'SELECT QUERY' 3. IN THE QUERY DESIGN VIEW DO THIS: 1ST ROW FIELD: SELECT FIELD THAT REPRESENTS THE ACCOUNT TABLE: SELECT YOUR TABLE HERE TOTAL: GROUP BY 2ND ROW FIELD: THE ONE WITH THE $AMOUNT TABLE: SAME AS ABOVE (YOUR ONLY TABLE PROBLABLY) TOTAL: SUM BY --> THIS IS IT IT WILL PRODUCE WHAT YOU WANT...ALL TOTALS BY ACCOUNT LATER...POST BACK WITH RESULTS..... ;-)

Thanks everyone for all your help! I really appreciate everyone looking into this. I found another solution that was posted by Anonymous user on another board. All your suggestion have saved me great amounts of time. Here is the solution I chose to go with: Here is some code (this is written in Excel 2000 and I don't have a copy of 97 to test it on.) As for the code, it's not pretty. Just a quick solution. To be able to make this work (note I'm basing this off of Excel 2000's UI, so it might not work for you): You will need to make visible the "Control Toolbox" command bar. Can do this by right clicking anywhere on the top tool bars, and selecting "Control Toolbox" Drag a CommandButton on to the worksheet somewhere. Right Click it and select view code. Copy all the code below to the editor over everything that exists. Make sure to change rEND to a row like row 5 in your first picture. For instance, if you wanted to test my code you could change rEND to 9 (using the data on the first picture). If you wanted, you could also make this code start in the middle of your data by changing both rBEGIN and rEND. (using first picture: rBEGIN = 5 rEND = 15) Once you copied this code over, save it in the Visual Basic Editor, and then you can click the button. Hope this works in 97... ******* START CODE *********************************** Option Explicit Const TOTALPRICE = 1 'Columns data is in Const PRICE = 2 Const LOCATION = 3 Const ACCOUNT = 4 Const rBEGIN = 2 'Begin Row of your data Const rEND = 9 'End Row of your data 'YOU NEED TO CHANGE rEnd to be very last row 'a row with a , or . in account Private Sub CommandButton1_Click() Call doTotal(rBEGIN,rEND) End Sub Sub doTotal(bRow As Long, eRow As Long) Dim rRow As Long 'Read Row Dim thisAccount As String Dim nextAccount As String Dim startRow As Long Dim endRow As Long Dim Break As Boolean Dim myFormula As String Dim i As Long startRow = bRow thisAccount = Trim(CStr(Cells(bRow, ACCOUNT).Value)) Break = False bRow = bRow + 1 For rRow = bRow To eRow nextAccount = Trim(CStr(Cells(rRow, ACCOUNT).Value)) If thisAccount <> nextAccount Then endRow = rRow - 1 myFormula = "=SUM(B" & startRow & ":B" & endRow & ")" For i = startRow To endRow Cells(i, TOTALPRICE).Formula = myFormula Next i Rows(rRow).Delete 'delete this row startRow = rRow 'reset startrow for next account thisAccount = Trim(CStr(Cells(rRow, ACCOUNT).Value)) rRow = rRow - 1 'decrement rRow for next iteration eRow = eRow - 1 'one less row to iterate End If If rRow = eRow Then 'Prevent Infinite Loop Exit For End If Next rRow End Sub ******* END CODE *********************************** This worked like a charm in 97! Thanks again everyone!