Results 1 to 5 of 5
  1. #1
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402

    My problem is with the MOD function, but only when it's in an Excel validation condition

    My problem is with the MOD function, but only when it's in a validation condition



    The MOD function here works fine,
    Current_Worksheet.Range("G8:G" & Mid(last_cell, 4, 3)).Formula = "=IF(OR(ISBLANK(A8),ISBLANK(C8),ISBLANK(D8),ISBLAN K(F8),ISBLANK(F8),MOD(MINUTE(C8),15)>0,MOD(MINUTE(D8),15)>0,MOD(MINU TE(E8),15)>0,MOD(MINUTE(F8),15)>0,(H8)<=0,(C8)>(D8),(D8)>(E8),(E8)>(F8),ISERROR(H8) ,ISERROR(I8),ISERROR(O8),ISERROR(P8)),1,IF(OR((C8) <0.270833333,(C8)>0.770833334),1,IF(OR((D8)<0.2708 33333,(D8)>0.770833334),1,IF(OR((E8)<0.270833333,( E8)>0.770833334),1,IF(OR((F8)<0.270833333,(F8)>0.7 70833334),1,10)))))"

    The ISBLANK function works fine, bu tif I try and change the function it fails

    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).Select
    With Selection
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).FormatConditions.Add Type:=xlExpression, Formula1:="=
    ISblank(A8:F" & Mid(last_cell, 4, 3)
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).FormatConditions(1).Interior.Color = 255
    End With

    What I'd like to be able to do is repalce the above ISBLANK function with the MOD function, but can't get the syntax correct.


    Used for when the cell is not blank and the value is not a multiple of 15 minutes
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).Select
    With Selection
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).FormatConditions.Add Type:=xlExpression, Formula1:="=
    MOD(MINUTE(A8:F" & Mid(last_cell,4,3)),15)>0
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).FormatConditions(1).Interior.Color = 255
    End With


    any help would be great.


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This is missing closing ) for the ISBLANK()

    Formula1:="=ISBLANK(A8:F" & Mid(last_cell, 4, 3) & ")"

    Try:

    Formula1:="=MOD(MINUTE(A8:F" & Mid(last_cell, 4, 3) & "),15)>0"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Sorry about the incomplete function before, I'm loosing track of were I am in this!

    I have this, but it returns the error - run-time error '9', invalid procedure call or argument

    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).Select
    With Selection
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).FormatConditions.Add Type:=xlExpression, Formula1:="=Formula(MOD(MINUTE(A8:F" & Mid(last_cell, 4, 3) & "),15)>0"
    Current_Worksheet.Range("A8:F" & Mid(last_cell, 4, 3)).FormatConditions(1).Interior.Color = 255
    End With


    this is the problem area, i can't seem to combine the MOD, MINUTE and MID functions, just can't get the syntax correct.

    =Formula(MOD(MINUTE(A8:F" & Mid(last_cell, 4, 3) & "),15)>0"

  4. #4
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Well I got that part going as shown below, now I just have to sort out the conditional formatting order for the cells that have more than one condition set.


    Current_Worksheet.Range("C8:F" & Mid(last_cell, 4, 3)).Select
    With Selection
    Current_Worksheet.Range("C8:F" & Mid(last_cell, 4, 3)).FormatConditions.Add Type:=xlExpression, Formula1:=
    "=MOD(MINUTE(C8:F" & Mid(last_cell, 4, 3) & "),15)>0"
    Current_Worksheet.Range("C8:F" & Mid(last_cell, 4, 3)).FormatConditions(3).Interior.Color = 49407
    End With

    Thanks for your help on this, I'll let you know how I get on with setting out the multiple conditions I have.

  5. #5
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Ok I have finished it... for now anyway.

    A sample of the finished sheet is here for anybody interested in multiple conditional formatting, time sheets, and so on.

    PS
    This has all been done from Access VBA, if anybody is interested in seeing the code just reply to this thread with a request.

    regards and thanks to all who helped with this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Problem with IIF function
    By donnysl in forum Queries
    Replies: 3
    Last Post: 08-12-2013, 10:11 AM
  2. Replies: 1
    Last Post: 11-26-2012, 12:35 PM
  3. Replies: 2
    Last Post: 10-31-2012, 11:52 AM
  4. Problem with IIF function
    By Hulk in forum Forms
    Replies: 3
    Last Post: 03-20-2011, 12:59 PM
  5. Acess 2007 Validation Problem- HELP!?
    By JackLowe in forum Access
    Replies: 1
    Last Post: 04-20-2009, 12:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums