Posts by jefsboys

jefsboys · Feb 1st 2007

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,

Jeff

jefsboys · Jan 19th 2007

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...

Code

Private Sub Workbook_open()


Dim wBook As Workbook
Dim UsrName As String


UsrName = Environ("username")
On Error Resume Next
Set wBook = Workbooks("Excel_Tools.xls")


If UsrName <> "j" Then
        If wBook Is Nothing Then 'Not open
            'just let it open
        Else 'It is open
            'Excel_Tools.xls is the name of the book trying to open
            'Trying to force it to open as read only with no dialog boxes
            Workbooks.Open Filename:="Excel_Tools.xls", ReadOnly:=True
        End If
Else 'if it is "j" that is opening it let him know if someone has it locked.
    'Do nothing and let Excel send a dialog box stating that it is open
    'if indeed it is.
End If


Windows("Excel_Tools.xls").Visible = False
Run "AddMenus"
End Sub

Display More

jefsboys · May 19th 2006

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

jefsboys · May 18th 2006

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.

jefsboys · May 18th 2006

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.

Code

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.

jefsboys · May 18th 2006

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.

Code

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


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

Thanks - Any other thoughts.

jefsboys · May 18th 2006

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.

Code

=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.

jefsboys · Jan 30th 2006

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.

Code

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

jefsboys · Jan 30th 2006

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.

Code

Sub Dups()
Dim cell As Range
Dim ClrNmbr As Long, i As Integer, ElementCount As Integer
Dim FoundMtch As Boolean
Dim UserRange As Range
Dim TempElement As Variant
Dim mySearchString As Variant
Dim Prompt As String, Title As String
Dim cel As Range, first As Range


Prompt = "Select numbers to check for Duplicates."
Title = "Select Cells"


'Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox(Prompt:=Prompt, Title:=Title, _
Default:=ActiveCell.Address, Type:=8) 'Range Selection
ClrNmbr = 3
  For Each cell In UserRange
        If cell.Value <> "" Then
            mySearchString = cell.Value
            With UserRange
                Set cel = .Find(What:=mySearchString, LookAt:=xlPart)
            '**************************
                If Not cel Is Nothing Then
                    Set first = cel
                    Set cel = .FindNext(After:=cel)
                '=================================
                    Do While Not cel Is Nothing And cel.Address <> first.Address
                        '------------------------------
                            For i = 1 To Len(cel) + 1
                                'If Mid(cel, i, 1) <> "" Then '  <> " " Then


                                If IsNumeric(Mid(cel, i, 1)) Then ' <> " " Then
                                    ElementCount = ElementCount + 1
                                    TempElement = TempElement & Mid(cel, i, 1)
                                Else
    ' >>>>>>>>>> The following line should evaluate to true on 14
                                    If TempElement = mySearchString Then
                                        'first.Interior.ColorIndex = ClrNmbr
                                        cel.Interior.ColorIndex = ClrNmbr
                                        FoundMtch = True
                                        TempElement = ""
                                        ClrNmbr = ClrNmbr = 1
                                    Else
                                    TempElement = ""
                                    End If
                                    
                                End If
                                
                            Next i
                        '------------------------------
                        Set cel = .FindNext(After:=cel)
                    Loop
                '=================================
                    If FoundMtch = True Then
                        ClrNmbr = ClrNmbr + 1
                    End If
                    
                FoundMtch = False
                End If
            '**************************
        End With
        End If
    Next cell
End Sub

Display More

Thanks in advance.

jefsboys · Jan 16th 2006

Re: Average Cells on varying number of sheets

Thank you again. That works great.

jefsboys · Jan 16th 2006

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.

jefsboys · Jan 13th 2006

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.

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

Both of you have a wonderful weekend.

God Bless

jefsboys · Jan 13th 2006

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.

Jeff

jefsboys · Jan 13th 2006

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.

Code

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

jefsboys · Jan 9th 2006

Re: Average Cells on varying number of sheets

Aaron,

Thank you very much.
Yes, now that i can see this I will be able to adapt to many other solutions.

Thank you - thank you - and thank you. I hope you had a good weekend.

Jeff

jefsboys · Jan 6th 2006

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.

jefsboys · Jan 6th 2006

Re: Average Cells on varying number of sheets

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

Code

LastRow = Worksheets(SheetName).Range("A65536").End(xlUp).Row
                
                  Workbooks(MonthlyBook).Worksheets(SheetName).Range("A" & LastRow + 2 & _
                    ":" & Cells(BtmCopyRange + LastRow, RtSideCol).Address) = Workbooks(CurrentBook). _
                    Worksheets(CrntFldr & "-" & DteMinus1Day).Range("A" & Range(TpLftTble).Row + 1 & ":" & BotRtTble).Value
                
                Worksheets(SheetName).Range("F1").Value = "CPU"
                Worksheets(SheetName).Range("G1").Value = "minutes > 70"
                
                DayOfWeek = Weekday(PrevDate)
                Worksheets("Wkdy").Activate
                
                For CPUUsage = LastRow + 2 To BtmCopyRange + LastRow
                    Range("F" & CPUUsage).Value = 100 - Range("E" & CPUUsage)
                    If Range("F" & CPUUsage).Value > 69 Then
                        Range("G" & CPUUsage).Value = 5
                    Else
                        Range("G" & CPUUsage).Value = 0
                    End If
                    Range("H" & CPUUsage).Value = DayOfWeek
                Next CPUUsage

Display More

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

Code

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.

Thanks.

jefsboys · Jan 6th 2006

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.

jefsboys · Jan 6th 2006

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?

Code

Range("B3").Select
    ActiveCell.Value = "=AVERAGE(Wkdy!F2,Wkdy!F290,Wkdy!F578,Wkdy!F866,Wkdy!F1154,Wkdy!F1442,Wkdy!F1730,Wkdy!F2018,Wkdy!F2306,Wkdy!F2594,Wkdy!F2882,Wkdy!F3170,Wkdy!F3458,Wkdy!F3746,Wkdy!F4034,Wkdy!F4322,Wkdy!F4610,Wkdy!F4898,Wkdy!F5186,Wkdy!F5474,Wkdy!F5762,Wkdy!F6050,Wkdy!F6338,Wkdy!F6626,Wkdy!F6914,Wkdy!F7202,Wkdy!F7490,Wkdy!F7778,Wkdy!F8066,Wkdy!F8354)"
    Selection.AutoFill Destination:=Range("B3:B288"), Type:=xlFillDefault
    
    Range("C3").Select
    ActiveCell.Value = "=MAX(Wkdy!F2,Wkdy!F290,Wkdy!F578,Wkdy!F866,Wkdy!F1154,Wkdy!F1442,Wkdy!F1730,Wkdy!F2018,Wkdy!F2306,Wkdy!F2594,Wkdy!F2882,Wkdy!F3170,Wkdy!F3458,Wkdy!F3746,Wkdy!F4034,Wkdy!F4322,Wkdy!F4610,Wkdy!F4898,Wkdy!F5186,Wkdy!F5474,Wkdy!F5762,Wkdy!F6050,Wkdy!F6338,Wkdy!F6626,Wkdy!F6914,Wkdy!F7202,Wkdy!F7490,Wkdy!F7778,Wkdy!F8066,Wkdy!F8354)"
    Selection.AutoFill Destination:=Range("C3:C288"), Type:=xlFillDefault




    Range("F3").Select
    ActiveCell.Value = "=AVERAGE(Wknd!F2,Wknd!F290,Wknd!F578,Wknd!F866,Wknd!F1154,Wknd!F1442,Wknd!F1730,Wknd!F2018,Wknd!F2306,Wknd!F2594,Wknd!F2882,Wknd!F3170,Wknd!F3458,Wknd!F3746,Wknd!F4034,Wknd!F4322,Wknd!F4610,Wknd!F4898,Wknd!F5186,Wknd!F5474,Wknd!F5762,Wknd!F6050,Wknd!F6338,Wknd!F6626,Wknd!F6914,Wknd!F7202,Wknd!F7490,Wknd!F7778,Wknd!F8066,Wknd!F8354)"
    Selection.AutoFill Destination:=Range("F3:F288"), Type:=xlFillDefault
    
    Range("G3").Select
    ActiveCell.Value = "=MAX(Wknd!F2,Wknd!F290,Wknd!F578,Wknd!F866,Wknd!F1154,Wknd!F1442,Wknd!F1730,Wknd!F2018,Wknd!F2306,Wknd!F2594,Wknd!F2882,Wknd!F3170,Wknd!F3458,Wknd!F3746,Wknd!F4034,Wknd!F4322,Wknd!F4610,Wknd!F4898,Wknd!F5186,Wknd!F5474,Wknd!F5762,Wknd!F6050,Wknd!F6338,Wknd!F6626,Wknd!F6914,Wknd!F7202,Wknd!F7490,Wknd!F7778,Wknd!F8066,Wknd!F8354)"
    Selection.AutoFill Destination:=Range("G3:G288"), Type:=xlFillDefault

Display More

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

Thank you very much for your continued thought.

jefsboys · Jan 5th 2006

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.

Thanks,
Jeff