VBA日期+逻辑 [英] VBA Date + For logic
问题描述
有人可以帮助我吗?看来我的逻辑完全没有用,因为它会在开始时间一直在12:00 AM返回我 这是我的代码
can someone help me? It seems my for logic is not working at all because it keeps returning me 12:00AM for starttime Here is my code
Sub forlogic()
Dim i As Single
Dim totalrow As Double
Dim startdate As Date
Dim mydate As Date
totalrow = Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown)).Rows.Count
mydate = Date
Debug.Print mydate
mydate = mydate - 90
Debug.Print mydate
For i = 1 To i = rowtotal Step 2
startdate = Format(Range("E" & i).Value, "short date")
startdate = Format(Date, "mm/dd/yyyy")
If mydate > startdate Then
Range("M" & i).Value = "Expired"
End If
i = i + 1
Next i
Debug.Print startdate
End Sub
不断回馈我
开始日期为12:00:00 AM
12:00:00 AM for startdate
推荐答案
下面的代码已经过测试,可以正常工作.更新列表:
Below code is tested and working as expected. List of updates:
1)要引用工作表Thisworkbook.Sheets("Sheet1")
,我将其暗化为ws
2)更新了totalrow
计算
3)由于您仅根据IF
语句的一个结果进行操作,因此可以精简为1行.
4)添加Option Explicit
以捕获诸如将totalrow
与rowtotal
交换之类的错误(此也可能会捕获您的工作表引用错误)
5)调整了i的增量(默认为1,无需声明Step 1
)
1) To refer to sheet, Thisworkbook.Sheets("Sheet1")
which I dimmed as ws
2) Updated totalrow
calculation
3) Since you are only acting on one outcome of the IF
statement, you can condense to 1 line.
4) Add Option Explicit
to catch errors like swapping totalrow
with rowtotal
(This also would have caught your sheet reference error)
5) Adjusted the i increment (this defaults to 1, no need to state Step 1
)
这已经过测试,并且可以正常工作.
This is tested and working fine on my end.
Option Explicit
Sub forlogic()
Dim totalrow As Long, i As Long
Dim startdate As Date, mydate As Date
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
totalrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
mydate = Date - 90
For i = 1 To totalrow
startdate = Format(ws.Range("E" & i).Value2, "mm/dd/yyyy")
If mydate > startdate Then ws.Range("M" & i).Value = "Expired"
Next i
End Sub
这篇关于VBA日期+逻辑的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!