Generate Weekending date
Results 1 to 7 of 7

Thread: Generate Weekending date
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Generate Weekending date

    Hi All,

    I am doing Visual Basic and would like to know how i can convert this statement to return the weekending date. The result will only give me a count of days from sunday to the date on transdate.value but not the weekending.
    WE.Value = Weekday(transdate.Value, vbSunday)

    Please help me out!
    Bryan

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,441
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Generate Weekending date

    Try this:
    Code:
    WE.Value = transdate.Value + (7 - Weekday(transdate.Value))
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Generate Weekending date

    Hi, it says Run-time error 13 - Type mismatch. Don't know what's wrong with this.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,441
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Generate Weekending date

    Does it make any difference if you remove the ".Value" references, i.e.
    Code:
    WE = transdate + (7 - Weekday(transdate))
    If not, please answer the following questions:
    How are WE and transdate declared?
    What data type are they?
    What is the value of transdate when the code runs?

    Here is a simple example showing it working:
    Code:
    Sub Test()
    
        Dim transdate As Date
        Dim WE As Date
        
        transdate = Date
        WE = transdate + (7 - Weekday(transdate))
        
        MsgBox WE
        
    End Sub
    Last edited by Joe4; Aug 21st, 2019 at 10:18 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Generate Weekending date

    Hi, still having the same error.

    1.
    If ComboBox6.Value = "PEGA sampling" Then
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
    .Cells(lRow, 19).Value = transdate.Value
    2. This is just a date transferred from excel sheet to a textbox
    3. i also have this when transferring data from excel sheet to the textboxes:
    With Me.ComboBox1
    .Clear
    Application.ScreenUpdating = False
    ListItems = employee
    ListItems1 = employee2
    For i = 1 To UBound(ListItems, 1)
    .AddItem ListItems(i, 1)
    .AddItem ListItems1(i, 1)
    Next i
    ComboBox1.ListIndex = 0
    End With

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,441
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Generate Weekending date

    I am not sure what you are trying to show me with the code in youre previous post, and what that has to do with your original question.

    Please post your original code, with my inserted function, and tell me which line of code is highlighted when the error occurs and you click "Debug".
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Generate Weekending date

    I'm so sorry for the confusion. I think I'll have to use your function instead since it still has the same idea. Thank you very much for the help.

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
  •