1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel VBA Enter Date

Discussion in 'Business Applications' started by computerman29642, Nov 11, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have the following code

    Code:
        Dim EndDate As Date
        
        EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    
            If ws.Range("K4") <> "" Then
                'Do Nothing
            [B]ElseIf Date = EndDate Then
                ws.Range("K4") = EndDate[/B]
            Else
                ws.Range("K4") = Date + (7 - Weekday(Date, 1))
            End If
    
     
  2. Sponsor

  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I am unable to get the bolded lines of code to work properly. What I am trying to accomplish is that if the last day of the month falls M-F, then I need that date entered instead of Saturdays date.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    From what I gather you are trying to place the last weekday of the month into a particular cell? If so, you can use a select statement with the Weekday function to accomplish what you want.

    Code:
    Sub PlaceDate()
    
    Dim EndDate As Date
        
    EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    
    Select Case Weekday(EndDate)
    
    'Sunday
    Case 1
    Range("K4").Value = EndDate - 2
    
    'Saturday
    Case 7
    Range("K4").Value = EndDate - 1
    
    'Mon-Friday
    Case Else
    Range("K4").Value = EndDate
    
    End Select
    
    End Sub
    Regards,
    Rollin
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    This is for a timesheet style worksheet. A normal week ends on Saturday.

    So, I want the K4 cell to contain Saturday's date normally, unless the last day of the month is during a workweek. Then I would like the last day of the month date to be entered into the K4 cell.

    If you look at December on the calendar, the last day of the month is on Wednesday. Instead of Saturday's date, K4 would contain Wednesday's date.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,911
    What is the point of this line

    Code:
     If ws.Range("K4") <> "" Then
    Why can't you just use a regular IF statement

    Code:
        Dim EndDate As Date
        
        EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
        
        If Date = EndDate Then
        Range("K4").Value = Date
        Else
        Range("K4").Value = Date + (7 - Weekday(Date))
        End If
    Regards,
    Rollin
     
  7. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    The problem with using a regular IF statement is that the current Date may not equal the last day of the month.

    For Example, If Date = 11/12/2008 (Todays Date for this example), but the last day of the month is 11/13/2008 then this line "If Date = EndDate Then" will be false and the K4 cell will contain Saturday's date.
     
  8. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    We may need to check for the last week as well.
     
  9. NaomiKoli

    NaomiKoli

    Joined:
    Nov 13, 2008
    Messages:
    3
    do u guys think u could help me?
     
  10. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    NaomiKoli, what do you need help with?
     
  11. valis

    valis Moderator

    Joined:
    Sep 24, 2004
    Messages:
    72,621
    naomi:

    Please respond here. You've got 3 other posts that I'm currently closing; posting duplicitous posts is only going to serve to get you slower response times as we don't know how has tried what.

    thanks,

    v
     
  12. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Is this something that is hard to do?
     
  13. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have come up with this code. Can anyone tell me if this the best way to perform this code or help me to modify the code to work better.

    Code:
        Dim EndDate As Date
        
        EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    
        Dim rng As Range
        Dim i As Integer, counter As Integer
    
        'Set the range to evaluate to rng.
        Set rng = Range("D8:H8")
    
        'initialize i to 1
        i = 1
    
        'Loop for a count of 1 to the number of rows
        'in the range that you want to evaluate.
        For counter = 1 To rng.Rows.Count
            
            If Range("K4").Value <> "" Then
                ' Do Nothing
            ElseIf Date + (2 - Weekday(Date, 1)) = EndDate Or Date + (3 - Weekday(Date, 1)) = EndDate Or Date + (4 - Weekday(Date, 1)) = EndDate Or Date + (5 - Weekday(Date, 1)) = EndDate Or Date + (6 - Weekday(Date, 1)) = EndDate Then
                Range("K4") = EndDate
            Else
                Range("K4") = Date + (7 - Weekday(Date, 1))
            End If
    
        Next
    
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/768169