Hi, I wonder if anyone can help?
I use a file checker in excel to check whether various various files have been dropped into their folder location - the files come in daily. I've created an isFileThere function which works perfectly at the minute, however instead of applying all the If statements to the whole sheet, I want to apply one of the If statements to column 0 only. This if statement is bolded below.
Function isFileThere(FileName As String, aDay As String, aMonth As String, aYear As String) As String
If (Len(aDay) = 1) Then 'If the length of the day is 1 number i.e. 9 instead of 10
aDay = "0" + aDay ' Then add a 0 before the day to make it 09, 07, 06, etc
End If
If (Len(aMonth) = 1) Then ' If the end of the month is 1 number i.e 9 instead of 10, 11, 12
aMonth = "0" + aMonth 'Then add a 0 before the month number to make it 09, 08, 07 etc..
End If
FileName = Replace(FileName, "[dd]", aDay) ' Find and replace the file name with (FileName,
FileName = Replace(FileName, "[mm]", aMonth)
FileName = Replace(FileName, "[yyyy]", aYear)
If (Dir(FileName) = "") Then 'If in the directory the file name is blank i.e. not there
isFileThere = "Missing!" 'Return the text "Missing!" to the cell
ElseIf (FileLen(FileName) = 0) Then 'If the file length or size of the file in question is 0
isFileThere = "0kb" 'Return the text "0kb" to the cell
'ElseIf (FileLen(FileName) < 20000) Then 'If the file length is less than 20,000kb
'isFileThere = "Small File!"
Else
isFileThere = "OK!" 'If neither of the two above statements are relative, then return the text "OK!"
End If
End Function
It is only this particular file that needs to be above 20,000kb, which is why I need it applying to column O only.
Any suggestions are much appreciated.
Thanks!
Bookmarks