none
Psuedo code RRS feed

  • Question

  • Hi Forum,

    I have the following requirement, I need this VBA/Macro to subtract the FROM and to Dates and calculate the number of weeks, if the number of weeks is greater than 5 then the excel range6 is visible.

    Sub new3()
    Dim Range1
    Dim Range2
    Dim Range3
    Dim Range4
    Dim Range5
    Dim Range6

    if((int(f9)-(int(c9)/7>5))Then

    Range1.Visible = True
    Range2.Visible = True
    Range3.Visible = True
    Range4.Visible = True
    Range5.Visible = True
    Range6.Visible = True
    Else
    Range1.Visible = True
    Range2.Visible = True
    Range3.Visible = True
    Range4.Visible = True
    Range5.Visible = True
    Range6.Visible = False
    End If
    End Sub

    Thanks in advance. 
    Monday, January 18, 2016 10:26 AM

Answers

  • Hi,

    Here is a simple code:

    Sub proFirst()
            Dim var As Double
            Dim val1 As Date
            Dim val2 As Date
            val1 = InputBox("Enter the value of Date")
            val2 = InputBox("Enter the value of Date")
            var = TestDates(val1, val2)
            If (var > 5) Then
           Range("A6:A6").EntireRow.Hidden = False
            Else
            Range("A6:A6").EntireRow.Hidden = True      
            End If
    End Sub
    Function TestDates(pDate1 As Date, pDate2 As Date) As Long
       TestDates = DateDiff("WW", pDate1, pDate2)
    End Function

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, January 19, 2016 10:04 AM

All replies

  • Hi Durbslaw,

    You can use DATEDIFF function.

    The Microsoft Excel DATEDIFF function returns the difference between two date values, based on the interval specified.

    The syntax for the DATEDIFF function in Microsoft Excel is:
    DateDiff( interval, date1, date2, [firstdayofweek], [firstweekofyear] )

    The interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.
    Interval Explanation
    yyyy Year
    q Quarter
    m Month
    y Day of year
    d Day
    w Weekday
    ww Week
    h Hour
    n Minute
    s Second

    Function TestDates(pDate1 As Date, pDate2 As Date) As Long
    
       TestDates = DateDiff("WW", pDate1, pDate2)
    
    End Function

    Regards

     Edward

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, January 19, 2016 5:50 AM
  • Hi Edward, it not the DateDiff bit that is  the issue, it hiding or unhiding a range based on the datediff function
    Tuesday, January 19, 2016 7:35 AM
  • Hi,

    Here is a simple code:

    Sub proFirst()
            Dim var As Double
            Dim val1 As Date
            Dim val2 As Date
            val1 = InputBox("Enter the value of Date")
            val2 = InputBox("Enter the value of Date")
            var = TestDates(val1, val2)
            If (var > 5) Then
           Range("A6:A6").EntireRow.Hidden = False
            Else
            Range("A6:A6").EntireRow.Hidden = True      
            End If
    End Sub
    Function TestDates(pDate1 As Date, pDate2 As Date) As Long
       TestDates = DateDiff("WW", pDate1, pDate2)
    End Function

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, January 19, 2016 10:04 AM
  • Thanks!
    Wednesday, January 20, 2016 6:01 AM
  • Thanks!
    Wednesday, January 20, 2016 6:02 AM
  • Hi Durbslaw,

    If your issue has been resolved, I suggest you mark the helpful reply as answer to close this thread, and then others who run into the same issue would find the solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, January 20, 2016 6:51 AM