CODEWITHSUNDEEP
vbaif-statement
Tyler
Tyler

Reputation: 616

If date greater than or equal then function

Public DateRng As String

Private Sub DateLookup()

'Dim ColCnt As Integer

'Prompt Date to look for
  DateRng = InputBox("Insert date in format dd/mm/yy", "User date", Format(Now(), "dd/mm/yy"))
  If IsDate(DateRng) Then
    DateRng = Format(CDate(DateRng), "dd/mm/yy")
    ColumnDateCheck
'    MsgBox DateRng
  Else
    MsgBox "Wrong date format"
    Exit Sub
  End If



End Sub

Private Sub ColumnDateCheck()

    For C = 3 To 29
        If Cells(C, 2) >= DateRng Then
            'Function
            Cells(C, 5) = Cells(C, 3) + Cells(C, 4)

        End If
    Next

End Sub

Data in which code is performing on

Test Sheet

Not having error executing code but function is not working as intended. It executes function in a mess without any pattern behind it. Can't understand output.

Objective

  1. InputBox in DateLookup sub prompts for date
  2. If entry is valid, call out ColumnDateCheck Sub
  3. Look for date entered in column B from Row 3 to 29. If date is greater or equal to, adds column C & D in Column E.

Upvotes: 1

Views: 13591

Answers (2)

ashleedawg
ashleedawg

Reputation: 21619

I don't use a USA date format (I use y/m/d) and neither do you apparently, since you're d/m/y and English/USA standard is m/d/y, so that was part of the problem (for both of us).

Regardless of how the cells are formatted in Excel, VBA still wants the date in a specific way. There are a few ways to deal with that; I chose the easy one: make the user enter the date how Excel is expecting it.

So I added a function that will specify the format (which would be different for you and I and your computer).

The other part of the problem is that you need to better understand data types. Basically, were trying to store a Date in a String variable, which doesn't always work as expected (like this time).

InputBox does return a string, so you do need to convert it, but converting it and still storing it in a String just keeps it a string.

Same with the statement: DateRng = Format(CDate(DateRng), "dd/mm/yy") since Format always returns a String (even if it "looks" like a date) and your DateRng variable is still a String.

Anyhow, this modified code should work:

Private Sub DateLookup()
    Dim str_DateRng As String
    Dim dateRng As Date
    str_DateRng = InputBox("Enter date in format " & DateFormat, "User date", _
        Format(Now(), DateFormat))
    If IsDate(str_DateRng) Then
        dateRng = CDate(str_DateRng)
        ColumnDateCheck (dateRng)
    Else
        MsgBox "Wrong date format"
        Exit Sub
    End If
End Sub

Private Sub ColumnDateCheck(dateToFind As Date)
    Dim c As Integer
    For c = 3 To 29
        If Cells(c, 2) >= dateToFind Then
            Cells(c, 5) = Cells(c, 3) + Cells(c, 4)
        End If
    Next c
End Sub

Function DateFormat() As String
    Select Case Application.International(xlDateOrder)
        Case 0 : DateFormat = "m/d/yyyy"
        Case 1 : DateFormat = "d/m/yyyy"
        Case 2 : DateFormat = "yyyy/m/d"
    End Select
End Function

Upvotes: 2

user5326167
user5326167

Reputation:

Try to compare the dates like this: 

   If Format(Cells(C, 2) ,"yyyy-mm-dd")>= Format(DateRng,"yyyy-mm-dd") Then......etc.

Upvotes: 1

Related Questions