如果日期大于或等于则函数 [英] 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

代码执行的数据

执行代码时没有错误但功能无法正常工作。它在一个混乱中执行函数，后面没有任何模式。无法理解输出。

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.

目标

1. DateLookup子菜单中的InputBox提示日期


2. 如果条目有效，则调出ColumnDateCheck Sub


3. 查找从第3行到第B列输入的日期29.如果日期大于或等于，则添加C和C列。列E中的D.

推荐答案

我不使用美国日期格式（我使用 y / m / d ）你也不明白，因为你 d / m / y 和英文/美国标准是 m / d / y ，所以这是问题的一部分（对我们两个人来说）。

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).

无论Excel中的单元格格式化如何，VBA仍然希望以特定方式使用日期。有几种方法可以解决这个问题;我选择了一个简单的方法：让用户输入Excel预期的日期。

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).

问题的另一部分是你需要更好地理解数据类型。基本上，试图在 String 变量中存储 Date ，该变量并不总是按预期工作（像这样时间）。

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 确实返回一个字符串，所以你需要转换它，但转换它仍然将它存储在String中只是保持字符串。

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.

与语句相同： DateRng =格式（CDate（DateRng），dd / mm） / yy）因为格式总是返回一个字符串（即使它看起来像一个日期）和你的 DateRng 变量仍然是一个字符串。

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

这篇关于如果日期大于或等于则函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！