Posts by jefsboys

    Re: Stop "file In Use" Dialog Box Except For Owner Of File.

    carlmack and MrkFrrl,

    I thank you both very much for taking the time to respond to such a simple question. I had in mind such awesome work to accomplish this and overlooked the basics.

    Sorry it took me so long to get back. It is good to be this busy at work. :)

    Thank you,


    My Goal: To have other users open a workbook on the network without having to click button to open it as read only.

    I have been trying to get rid of the "File In Use" message for the other users since they only need a read only copy. I am the only one who needs to know if it is in use because I will make changes to the workbook tools and have to save them.

    I have set the Save As preferences so that it requires a password for modification and checked the read only checkbox. But that dialog box is popping up for the users. I am trying to eliminate any file in use dialog box for users other than myself.

    Here is my workbook open procedure...

    Re: SUMPRODUCT not calculating

    Excellent, many thanks for that. I updated my code and ran the process and it worked like a charm.

    I thank you both Wigi and Mr. Pope for your attention to my post.

    God Bless

    Re: SUMPRODUCT not calculating

    The column A data on the "Data" sheet is the raw data as pulled from a log file, it varies a minute or so either way and that is why I put the MROUND formula in column K, I did that so that I would have a value to exactly match the values on the "Average" sheet and avoid the problem I am having. :confused:

    I hope I was able to make that sound sensible.

    Re: SUMPRODUCT not calculating

    I just saw your last post Andy which made me look at something...

    On the "Data" sheet below is the formula for where that value in column K comes from. I don't know how to change it be more exact like the values in the "Average" sheet.

    Range("K" & CPUUsage).FormulaR1C1 = "=MROUND(RC[-10],1/24/60)"

    After those formulas are processed I do a copy and paste special values.

    Wigi and Mr. Pope I appreciate your help.

    The copy and paste worked for me also, but this is a stand alone process that runs 365 days a year. I am hoping to try and nail this down.

    Re: SUMPRODUCT not calculating

    I thank you both for your attention to my issue.

    I didn't have the same luck on doing the paste special values. I still had to put the cell in edit mode before the formula would recognize it.

    Below is the code I use to format those cells, it is identical. If you go to format cells it also shows them to be identical.

    Range("K:K").NumberFormat = "h:mm:ss;@"
     Range("B:B").NumberFormat = "h:mm:ss;@"

    Thanks - Any other thoughts.

    In the attached extremely slimmed down workbook...

    I have two sheets; Data and Average. The "Average" sheet points to the "Data" sheet for it's data.
    I have some cells with formulas highlighted in the "Average" sheet that are not calculating, although if you look at the "Data" sheet there is data in there that should be calculating.
    Here is one of the formulas from the "Average" sheet that is not recognizing it's match in the "Data" sheet.

    =SUMPRODUCT(--(Data!$K$3:$K$9176 = Average!$B11),  --(Data!$H$3:$H$9176 < 6)  )

    This is a stand alone process that is kicked off at 2am. Normally the "Data" sheet would have around 9000 rows of data to be calculated.

    For grins I was playing around with this and was able to get the formulas in the "Average" sheet to recognize the data in the "Data" sheet by putting the cell in the "Data" sheet into edit mode. I don't understand.

    Thanks in advance for your help.

    Re: Find Duplicate (xlPart) within string

    I did try that, I was thinking the same thing.

    It would not find this “143 555”, and those are the ones I need to find also. I am finding them ok.

    With the two variables below, I have stepped through the code and seen it display variable TempElement = "14" and mySearchString = 14 (please note quotes around 14) but not step into that part of the If statement.

    If TempElement = mySearchString Then 
                'it won't step into here
                cel.Interior.ColorIndex = ClrNmbr 
                FoundMtch = True 
                TempElement = "" 
                ClrNmbr = ClrNmbr = 1
                TempElement = "" 
    End If

    I looked over many of the threads on this forum but was unable to piece this together.

    I have a range of cells I will select (InputBox); I will take the value of cell 1 and search the rest of the range for that exact value, then cell 2 and so on. The value cannot be part of a larger number, it must be exact. The cells are space delimited. Example.

    Cell 1 conatins 14.
    I find 14 in another cell and it shades the interior red.
    I do a find next and it finds another cell containing 14, the value of that cell is “14 230”. This should get shaded red.
    I do another find next and the value of that cell is “143 555”. Now because I am using xlPart it finds this cell. But this cell shouldn’t get shaded red because the delimited value is 143.

    I have most of the code for this running pretty smooth in the attached workbook. When I evaluate the search string to the found value and they appear the same it does not enter my If – Then statement.

    Here is the code module, I probably have done a lot of unnecessary work.

    Thanks in advance.

    Re: Average Cells on varying number of sheets

    I'm sorry, I have really been trying to get this to work for at least 6 hours. Both of you have wonderful examples but I need to be able to narrow it down by column "H" as seen in the first part of this formula below. One set will be created with the criteria less than the 6 and then in a few columns over I will change that to >= 6.

    =SUMPRODUCT((Data!$H$2:$H$10000 < 6),(Data!$K$2:$K$10000=Average!$B9),(MAX(Data!$F$2:$F$10000)))

    Aaron and Domenic I really appreciate your patience.

    Re: Average Cells on varying number of sheets

    I still don't see how the formula works, but it does. And for that I am very grateful. I will stare it down some to see if I can put order into the mechanics.

    Thank you very much for the explanation, more good information to hold on to.

    Both of you have a wonderful weekend.

    God Bless

    Re: Average Cells on varying number of sheets

    I attached a sample of the workbook with the code inside. I have some sample data in the "Data" sheet and the "Average" sheet is where it will be populated. If you run the code in the one and only module it will show you.

    I have seen you use the comment "confirmed with CONTROL+SHIFT+ENTER" before, I honestly don't know what that is. If there is brief explanation would you please share?

    Thank you very much for looking at this.


    Re: Average Cells on varying number of sheets

    I have been trying to get the following formula so that I can build the range for the MAX formula to look only at my criteria which is located in another column, is this possible?
    Right now the formula below goes into 287 rows and all rows show the same value. I am wanting to have it so that "Average!$B9" is the only thing looked at for the MAX data that is to be considered.

    =MAX((Data!$K$2:$K$10000 = Average!$B9), (Data!$F$2:$F$10000))

    Re: Average Cells on varying number of sheets

    I left the weekday values at the default (Sunday = 1 and so on)

    The table in the workbook has everything you wanted in it and will be good for me to work with also. If I didn't get the correct data in the table please let me know and I will correct it right away.

    I highlighted a couple of the repeated cells to show where a new day might begin and that the data where normally is the same in the Time column could vary. What will remain constant is the number of entries per day, and that will be 287. For this sample I only put 7 entires in from each day.

    Re: Average Cells on varying number of sheets

    Here is a snippet from the code which pulls the data in and performs some calculations...

    Below is a sample of the data after it is put in the table from the code above.

    Time	%usr	%sys	%wio	%idle	CPU   > 70	day
    0:05:00	3	8	5	84	16	0	4
    0:10:00	4	9	3	85	15	0	4
    0:15:01	4	8	2	86	14	0	4
    0:20:00	6	10	3	81	19	0	4
    0:25:01	4	8	2	86	14	0	4

    I am stumped on how to get the average CPU and max CPU from this 8000+ row table now and be able to seperate the weekdays from the weekends. Where the time 0:05:00 is listed above it will also be listed in the table how ever many number of days there are in that month, I will need to get the average of that time for each instance of 0:05:00 in the table.

    Where as the above sample begins with 0:05:00 that will continue on down to 23:55:00. And that will happen as many days as there are in the month.

    If this is too many questions on this please say so.


    Re: Average Cells on varying number of sheets

    Thank you very much Aaron.

    I can get all the data in the one sheet, each night I open that file up I will just go to the end of the recordset and import the next file.

    My thinking on running the formula is that I would have the results (Average and Max) isolated and I could call for the creation of the chart pretty easily by simply pulling a block range for the source data.

    This runs during the wee hours of the morning when no one is here and it creates the daily and soon to be monthly charts and saves them as HTML pages so that we can view these charts from anywhere with just a web browser.

    I can see what you are saying about making just one database sheet is best, now I am looking at the ease of calling the source range of the average and max data for the creation of those charts.

    I am going to dive into this and see what I can get done.

    I thank you very much for you help.

    Re: Average Cells on varying number of sheets

    Hind sight... thought never crossed my mind.

    Maybe you could help me with this and I think I can get it.
    I was thinking three sheets total; "Wkdy", "Wknd", and "Average". Self explanatory. Or do you still see it being easier doing this all in just one sheet and adding the date in a column? At this point the only identification of the data needs to be either weekday or weekend. I already have daily charts created automatically each night and that is when I am pulling the data aside and storing it for these monthly reports.

    When the calculations are made the sheets will have between 8000 and 9000 rows (288 rows per day). My formulas will need to be able to accept 31 different cell references (up to 31 days in a month), currently it is stopping me at 30.

    Below is what I was trying to do, is there an alternative way?

    The attached file has some sample data and the code in it.

    Thank you very much for your continued thought.

    Re: Average Cells on varying number of sheets

    I will stare these down for a while and put one together. I noticed a few things I will read up on to gain a better understanding.

    The data is put in those sheets by a different part of the code that I assembled; therefore it is ok to put references in those sheets such as putting "Wkdy" in cell H1.

    I thank you both tremendously. I will get it to work from your input.