excel - 在 VBA 中用今天的日期替换错误 #N/A

Question

我正在寻找替换 #N/A (由于查找搜索而导致)使用以下代码显示今天的日期，但它不起作用。您能否告知此逻辑是否有效或需要应用其他一些技术。

Const StartRow As Byte=2
LastRow = Range("J" & Rows.count).End(xlUp).Row

For i = StartRow To LastRow

    myValue = Range("B" & i).Value

    If myValue =Specialcells(xlCellTypeFormulas,xlErrors) 
          Then Range("B" & i).Value = "=Today()"

  Next i

Answer 1

用日期或现在替换错误

第一个代码使用循环并将所有错误值替换为“日期”(当前日期)或“现在”(当前日期和时间)。

要使用 SpecialCells，您无需遍历行，而是将其应用于整个范围。这显示在第二个代码中。

代码

Option Explicit

Sub replaceWithDate()

    ' Constants
    Const LastRowCol As Variant = "J"  ' Last Row Column Index
    Const StartRow As Long = 2         ' Start Row Number
    Const CriteriaCol As Variant = "B" ' Criteria Column Index
    
    ' Define the row of the last non-blank cell in column 'J' ('LastRow').
    Dim LastRow
    LastRow = Range(LastRowCol & Rows.Count).End(xlUp).Row

    ' Additional variables to be used in 'For Next' loop.
    Dim myValue As Variant ' Current Value
    Dim i As Long          ' Row Counter
    
    ' Loop through rows.
    For i = StartRow To LastRow
        ' Write each value to Current Value.
        myValue = Range(CriteriaCol & i).Value
        ' Test if Current Value contains an error value.
        If IsError(myValue) Then
            ' Write 'Today()'
            'Range(CriteriaCol & i).Value = "=Today()"
            ' If you use the previous line, then when you open
            ' the worksheet tomorrow, it will have tomorrow's date.
            ' Write 'Date'. You can also use 'Now' to include time.
            Range(CriteriaCol & i).Value = Date ' Now
            ' Additionally you can change the number format:
            ' e.g. for Date
            'Range(CriteriaCol & i).NumberFormat = "mm/dd/yyyy"
            ' e.g. for Now
            'Range(CriteriaCol & i).NumberFormat = "mm/dd/yyyy hh:mm:ss"
        
        End If
    Next i

End Sub

Sub replaceWithDateSpecialCells()

    ' Constants
    Const LastRowCol As Variant = "J"  ' Last Row Column Index
    Const StartRow As Long = 2         ' Start Row Number
    Const CriteriaCol As Variant = "B" ' Criteria Column Index

    ' Define the row of the last non-blank cell in column 'J' ('LastRow').
    Dim LastRow
    LastRow = Range(LastRowCol & Rows.Count).End(xlUp).Row
    
    ' Define Criteria Range ('rng').
    Dim rng As Range
    Set rng = Range(CriteriaCol & StartRow, CriteriaCol & LastRow)
    
    ' Apply 'SpecialCells'.
    On Error Resume Next
    rng.SpecialCells(xlCellTypeFormulas, xlErrors).Value = Date
    On Error GoTo 0
    
End Sub