Posts by Robert B

Re: Networkdays Function

Hi Weasel

Thanks for your comments. The value of n refers to the range D2:AH2, where the value exceeds the days in the current month the target cell/s will be blank so will not increment the count. The problem of having 4 or 5 Sats in a month I had hoped to circumvent by including non-working saturdays in the list of Holidays. Unfortunately, the 28 day cycle idea is not feasible in the current requirement, worse, I now learn that working saturdays are a matter of negotiation on the day before, and, even then, honoured more in the breach than the observance.

So, I have come to the conclusion that I cannot cater for all these variables and so will have to accept that there is a certain amount of post-event adjustment.

I would still like to know what it is about the code I tried to use, particularly the COUNTIF statement.

thanks again for all your input and interest

Regards
Rob

Re: Networkdays Function

Hi Weasel

I have created an attendance record, 1 sheet per month, which will be completed on a daily basis by entering a 1 for attendance or a selection of single alpha codes indicating Holiday, Sickness, Training absences. The networkdays function works perfectly in calculating the total number of available working days for each month, taking into account Bank and Statutory holidays which I have stored in the workbook as a range named "Banks".

The problem arises because there are those employees who work 1 Saturday in 4, so I thought that amending the current function to include all Saturdays and then assuming that 3 Saturdays of 4 would be considered "holiday" and creating 4 new holiday lists (1 for each shift pattern) would solve the difficulty.

I came up with this

Function SatWorkDays(StartDate, EndDate, Range, Holidays)


Dim i As Integer
Dim n As Integer
Dim j As Integer
Dim myRange As Range
myRange = Range
i = 0
n = 1
Do Until n = 31
j = Application.CountIf(Holidays, myRange.Cells(n))
i = i + j
n = n + 1
Loop






SatWorkDays = ((EndDate - StartDate) + 1) - (i)


End Function

Where the Range is the list of days in current month, and Holidays is the named list of Banks & Stats

which, naturally, does not work, but I'm blowed if I can see why.

My apologies if this is not as clear as I think it is, your comments would be much appreciated.

Regards

Rob

Re: Networkdays Function

Hi Weasel

Thanks for that, the problem I now have is recreating the Holidays that NETWORKDAYS allows for.

Any suggestions?

Thanks for your help
Rob

Hi All

Is it possible to create a new function based on the NETWORKDAYS function so as to include Saturdays in the result?
Regards
RB

Re: List filenames

Thanks Dave for your time and assistance

Robert

Re: List filenames

Hi

I have used these macros to list the contents of a large number of zip discs I have which contain .tif files. Some of the files are quite large, as the original scanned picture was quite large. I wondered if it would be possible to add a bit of code to show the file size.

Regards

You could insert a column to the left of your work area and fill these cells with the colours of your choice. When printing exclude this column from your print selection

RB

I don't know how to control Excel from ASP, but if you what to ensure that a workbook opens in Excel with a default Zoom of 75% then go to

Tools>Macros>Edit

find "VBAProject(yourdocname)

expand the selection by clicking on the +

Double-click "This Workbook"

Select "Workbook" from the left drop-down,
"Open" from the right drop-down

and type

ActiveWindow.Zoom = 75
under Private Sub Workbook Open()

Hope this is of some use

Robert

Possibly the best idea would be to carry out the precise steps you have listed but with the Record Macro option activated.

This can be found at Tools>Macros>Record new Macro.

RB

Enter this formula in the adjacent column

=COUNTIF(A1:A10,A2)

where A1:A10 is the range containing the suspected duplicates and A2 is the cell to the left of the formula.

Then filter with >1 as the selection parameter and delete the results, or with 1 as the selection parameter and copy the results to a new location

Hope this is of use

Robert

This formula gives what I think you want

=A1 & ".jpg" where A1 contains the number

Hope this helps

Robert

Hi
you could try the following macro
[code]

Sub TimeDiff()

Dim i As Double, j As Double, t As Double
'i represents the value of the time, t is 10 minutes later and j is the accumulation of value

Range("A1").Select
ActiveCell.Offset(1, 0).Select

i = ActiveCell
t = i + 0.00694 'this is 10 minutes expressed as part of 24 hours

Do While ActiveCell <> "" 'loops while there is a value in the Time column

j = j + ActiveCell.Offset(0, 1) 'accumulates total in Value column

If ActiveCell >= t Then

ActiveCell.Offset(0, 2) = j 'Prints the value of j in column next to Value column
ActiveCell.Offset(1, 0).Select
i = ActiveCell
t = i + 0.00694
j = 0 'Resets variables

Else
ActiveCell.Offset(1, 0).Select

End If

Loop
End Sub
code/

hope this gives you something to work with

Regards

Robert

If I have correctly understood your requirement this formula would help

=SUMIF($B$5:$D$9,B11,$C$5:$E$9)
where B11 contains e.g. zelda
RB

HI Reg
You could try filtering out the NonC items copy & paste the rest to a second sheet and apply a Pivot to that data

RB

Hi

You could solve your problem by transposing Rows and Columns, unless you have more than 65000 clients.

If you are using VBA to create your Pivot there is a facility to make fields invisible.

In this case

set myItem = myField.PivotItems("Client")
'this assigns the item for Client to a variable
myItem.Visible = False

Hope this helps

A bit inelegant but you could start by adding a column to extract the month number from the date (=MONTH(A2)) and then use subtotals to find the month end and then run the following

code/
Sub DayAvg()

Dim myRange As Range

Do Until myRange = "Grand Total"
Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Set myRange = ActiveCell

ActiveCell.Offset(0, 1).Select


ActiveCell.FormulaR1C1 = "=AVERAGE(R[-1]C:R[-5]C)"


Application.Goto myRange
Loop


End Sub
code
this would place the average in the same column as the values but it could be put anywhere.

Hope this helps

Hi las

thanks for all your input to this minor problem. I do not appear to be able to set up groups of accounts only groups of addressees. I have set all other options I can to those you suggest and now, coincidentally, I am unable to access my mail at all. This is a common problem with my ISP.

I have managed to access my mail for 1 account via the ISP's website and deleted one of the mails from there. The ISP denies all knowledge of my second account, but that is a separate issue.

Thanks again for your assistance

Robert

Hi

thanks for that suggestion, it seemed promising. However, Outlook's advanced options allow me only to limit the size of a message. There is an option which allows me to keep a copy messages on the server which may be deleted after a selected period. If I were to implement this option and set it to say 1 day would thta delete these mails, considering they have been there for months now?
thanks again for your help

RB

Hi all

I have 2 mails on my providers server which do not download as they are larger than the amximum I have set to receive mail. They are pretty certainly the result of a virus that has automatically sent them to my address, so I dont want to reset my max (they are 3.75Mb). The iSP has ignored my request to deal with them. Is there any way they can be removed.

Thanks

Yes I suppose that was a vital bit of info. Start the macro in the cell above the first date on the Summary sheet. 2 other things, name/rename the Kickoff column in Data "Kickoff" ; I also changed some of the dates in the Data!Kickoff column to Jun-04 so the macro returned more than 1 PM.
As I said I have put some hard code into the macro and it applies only to Jun-04, but I it can be generalised and stuck inside a Do Loop.

Robert