Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 41

Thread: VBA Code Add Condition
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Jul 2010
    Posts
    741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Hi Fluff,

    Code Is Working Fine. I amended a code a bit for my additional requirements.


    From This
    Code:
    If .Offset(, 1).Value = "" And .Value <= Date Then

    To This - Coz I wanted another criteria i.e. if Date is blank then nothing should happen
    Code:
    If .Offset(, 1).Value = "" And .Value <= Date And .Value <> "" Then


    From This
    Code:
    .Offset(, 1).Value = Date - .Value

    To This - Coz I wanted text like 45 Day(s)
    Code:
    .Offset(, 1).Value = Date - .Value & " Day(s)"
    So far its working fine. I am working on few more things like if the days difference is a single day then Day should be there instead of Days and if the difference is 0 then it should show Today etc.

    First I will try then will bother you if I fail to accomplish.

    Keep you posted.

    Regards,

    Humayun
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,168
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Add Condition

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Jul 2010
    Posts
    741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Hello Fluff

    This is what I have come up with

    Code:
      
    If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 And .Value <> "" Then               .Resize(, 2).Font.ColorIndex = 3
                   .Offset(, 1).Value = Date - .Value & " Days"
                
                ElseIf .Offset(, 1).Value = "" And .Value < Date And Date - .Value = 1 And .Value <> "" Then
                   .Resize(, 2).Font.ColorIndex = 3
                   .Offset(, 1).Value = Date - .Value & " Day"
                
                ElseIf .Offset(, 1).Value = "" And .Value = Date And .Value <> "" Then
                   .Resize(, 2).Font.ColorIndex = 3
                   .Offset(, 1).Value = "Today"
    If date difference is > 1 day Then For example 2 Days
    If date difference is = 1 day Then 1 Day
    If date difference is = 0 i.e. current date Then Today


    I am not sure if there is a better way to write it...
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,168
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Add Condition

    If it works, it's good
    Added to which you understand it, so easier for you to modify in future if needed.
    Last edited by Fluff; Jul 15th, 2019 at 10:34 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    Board Regular
    Join Date
    Jul 2010
    Posts
    741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Hi Fluff,

    I am having a problem. The data on which this code is being applied is copied from a different worksheet to the current worksheet.

    i.e. copy then paste special > values.

    Some are results of formula. For example
    =IF(A1=B1,"")
    and if the logic is met then the answer returns
    ""
    - then it is copied and then paste special as value to the current worksheet. So when this happens the code debugs whith the first line of the code highlighted and giving a msg > runtime error 13 - type mismatch


    Code:
     If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 And .Value <> "" Then        
            .Resize(, 2).Font.ColorIndex = 3
            .Offset(, 1).Value = Date - .Value & " Days"
                
         ElseIf .Offset(, 1).Value = "" And .Value < Date And Date - .Value = 1 And .Value <> "" Then
            .Resize(, 2).Font.ColorIndex = 3
            .Offset(, 1).Value = Date - .Value & " Day"
                
         ElseIf .Offset(, 1).Value = "" And .Value = Date And .Value <> "" Then
            .Resize(, 2).Font.ColorIndex = 3
            .Offset(, 1).Value = "Today"
    Kindly let me know how to overcome this issue
    Last edited by hrayani; Jul 16th, 2019 at 03:58 AM.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,168
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Add Condition

    Check that you don't have any errors in the cells. ie #N/A, #VALUE! etc
    Last edited by Fluff; Jul 16th, 2019 at 07:51 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular
    Join Date
    Jul 2010
    Posts
    741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    Quote Originally Posted by Fluff View Post
    Check that you don't have any errors in the cells. ie #N/A, #VALUE ! etc
    No there are no errors as I have applied iferror on all formulas
    Last edited by hrayani; Jul 16th, 2019 at 08:48 AM.
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,168
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Add Condition

    In that case you will need to check that the cell isn't "" before you subtract it from Date.
    Code:
                If .Value <> "" Then
                   If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 Then
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    Board Regular
    Join Date
    Jul 2010
    Posts
    741
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code Add Condition

    I tried but getting debug msg. Compile error ... End with without with


    Also I wanted if the cell has "Not Applicable" in there then the code should not turn it to red

    I did a bit of experiment and came up with this
    Red Parts added in the code.

    Code:
      On Error Resume Next            
        If .Offset(, 1).Value = "" And .Value < Date And Date - .Value > 1 And .Value <> "" Then
         .Resize(, 2).Font.ColorIndex = 3
         .Offset(, 1).Value = Date - .Value & " Days"
                
        ElseIf .Offset(, 1).Value = "" And .Value < Date And Date - .Value = 1 And .Value <> "" Then
         .Resize(, 2).Font.ColorIndex = 3
         .Offset(, 1).Value = Date - .Value & " Day"
                
        ElseIf .Offset(, 1).Value = "" And .Value = Date And .Value <> "" Then
         .Resize(, 2).Font.ColorIndex = 3
         .Offset(, 1).Value = "Today"
                
        
         
                Else
                   .Resize(, 2).Font.ColorIndex = 1
                End If
             
             If .Value = "Not Applicable" Then
             .Resize(, 2).Font.ColorIndex = 1
             
             End If
             End With
          Next rngCell
       Next I
    Kindly let me know if its OK
    The tragedy of life doesn't lie in not reaching your goal. The tragedy lies in having no goal to reach.

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,168
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: VBA Code Add Condition

    Let's stick to one problem at a time. You have not changed the if statements as I showed.
    Also NEVER use "On Error Resume Next" in that manner, as it will simply hide problems.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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