Calculate totals within and based on information in 3 worksh NewbieOfMacros Junior Member Posts: 4 Threads: 2 Joined: Jul 2012 07-16-2012, 08:39 PM Hello, I am in need of assistance to write a macros that completes all these steps listed below: 1.Count # of entries/pumps in sheet1 2.Look at sheets “North, South, East” (can be individually) 3.Find all dates (as stated in MTBR worksheet C2 & C3)) within specified time (For example July 11,2011 – July 11,2012) 4.Then find if any pumps under the heading ‘equipment #’ match the ones in sheet1 (sheet1 headers are divided like this “north:equipment #’s, south:equipment #’s , east:equipment #’s etc.) 5.When it finds one, mark it as 1 failure depending on the sub-heading (HCU,HCF,HDA) the entry was found under, which are located on cells G3,5,7,9,11,13,15,17,19 for North; cells G3,5,7,9,11,13,15,17,19 for South; and cells G3,5,7,9,11 for East; it will add 1 to that # each time it finds a new one. And after its done that, the total number (based on each sub-headings values added together) for each sheet will populate cell G2. 6.Once that’s done for each worksheet, get it to go to MTBR sheet. 7.And for the cells labelled “North MTBR, South MTBR, East MTBR, and Site MTBR” it has to calculate MTBR. 8.Using this equation: a.North MTBR: G2 (In North Worksheet)/ # of pumps installed (based on the count total it did at the beginning in sheet1) * difference between dates/365 (dates are found in C2 & C3) b.South MTBR: G2 (In South Worksheet)/ # of pumps installed (based on the count total it did at the beginning in sheet1) * difference between dates/365 (dates are found in C2 & C3) c.East MTBR: G2 (In East Worksheet)/ # of pumps installed (based on the count total it did at the beginning in sheet1) * difference between dates/365 (dates are found in C2 & C3) d.Site MTBR: =sum(North,South,and East MTBR totals) I really hope someone can assist me as I know very little about writing code and this seems extremely difficult and I cannot do it by myself. I hope I have provided enough information. Thank You So Much! Reply Reply Gintaras Administrator Posts: 11,002 Threads: 128 Joined: Dec 2002 07-17-2012, 05:55 AM can start from this Macro Macro1783 Code: ```;/exe 1 ;assume we have 3 sheets that we will need to work with. Connect to each sheet using 3 ExcelSheet variables: ExcelSheet es1.Init("name of sheet 1") ExcelSheet es2.Init("name of sheet 2") ExcelSheet es3.Init("name of sheet 3") ;get number of rows in first sheet int nr nr=es1.NumRows out nr ;;show in QM output ;get all cells in sheet1. Don't know whether we need it now, but here is just an example, because we'll need to get cells anyway, using this or some other function ARRAY(str) a es1.CellsToArray(a) ;show all cells in QM output int c r for r 0 a.len ,out "-- row %i --" r+1 ,for c 0 a.len(1) ,,out a[c r]``` Reply Reply NewbieOfMacros Junior Member Posts: 4 Threads: 2 Joined: Jul 2012 07-17-2012, 04:14 PM Hi there, thanks for your reply. I find this code you gave me to makes no sense lol I will definitely need assistance working this out. Is there a way to send you the workbook I'm working with so that you can understand what I'm working with. I believe what you sent me assumes I know a hell of a lot more than I do, I know nothing. All I can do is record macros pretty much so I will honestly need someone to write it for me, really. Thanks Reply Reply Gintaras Administrator Posts: 11,002 Threads: 128 Joined: Dec 2002 07-17-2012, 04:51 PM This task is complex and difficult to understand for other person like me. Using ExcelSheet is the best way to work with Excel (fast and reliable), but often instead can be used key commands + copy/paste. If you are going to automate Excel in the future too, I recommend to learn how to do it with ExcelSheet. Start from something simple, for example create a macro that gets or sets a cell. I can help only with particular steps. Begin to create the macro, and ask for assistance (here) when you don't know how to do something. Choose any method - ExcelSheet or key/copy/paste. Reply Reply « Next Oldest | Next Newest »