Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Calculate totals within and based on information in 3 worksh
#1
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!
#2
can start from this
Macro Macro1783
Code:
Copy      Help
;/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]
#3
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
#4
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.


Forum Jump:


Users browsing this thread: 1 Guest(s)