+ Reply to Thread
Results 1 to 1 of 1

How to apply custom function to one Column only

  1. #1
    Registered User
    Join Date
    05-15-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to apply custom function to one Column only

    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!
    Last edited by lindomsm; 07-11-2013 at 10:16 AM. Reason: more clarity!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1