在整个工作簿中查找日期（以字符串的形式显示） [英] Finding a date (dimmed as string) in entire workbook

问题描述

我有以下代码：

  Sub Aftekenen（）
 
 Dim Sh As Worksheet 
 Dim Loc As Range 
 Dim Datum As String 
 
 Datum =28-12-2015
 
对于每个Sh在ThisWorkbook.Worksheets 
与Sh.UsedRange 
 
设置Loc = .Cells.Find（What：= Datum）
 
如果不是Loc Not Not Then然后
直到Loc没有
 Loc.Value =Yes
设置Loc = .FindNext（Loc）
循环
如果
结束
设置Loc = Nothing 
下一个
 
 End Sub 
  

然而，代码运行，但是它永远不会达到For每个循环。当我删除 - ，所以日期将成为一个数字，它找到它。然而，当破折号出现时，代码没有找到任何拟合值，但是这些值都存在。

解决方案

尝试这个，code>每个...下一个方法与 .Text （不是值，因为<$ c $的单元格的值c>28-12-2015是 42366 ），它的效果很好，但有些用户有一些反对这种方法的原因，因为对于我较少的编码更好的阅读

  Sub Aftekenen（）
 Dim Sh As Worksheet， Loc As As Range，Datum As String 
 Application.ScreenUpdating = 0 
 Datum =28-12-2015
对于每个Sh在ThisWorkbook.Worksheets 
对于每个Loc在Sh。 UsedRange 
如果Loc.Text = Datum Then Loc.Value =Yes
 Next Loc 
 Next Sh 
 Application.ScreenUpdating = 1 
 End Sub 
  

更新

如果您喜欢 .find 方法然后为searchin由于日期在 .text Application.FindFormat.NumberFormat 格式化为具有日期的单元格格式c $ c>是 18-05-2015 ，但 .value 是 42142

以下是您更新的变体

 子Aftekenen（）
 
 Dim Sh As Worksheet 
 Dim Loc As Range 
 Dim Datum As String 
 
 Application.FindFormat.NumberFormat =dd-mm- yyyy
 
 Datum =28-12-2015
 
对于每个Sh在ThisWorkbook.Worksheets 
与Sh.UsedRange 
 
设置Loc = .Cells.Find（Datum，，xlValues，，，，True，，True）
 
如果不是Loc Not Not Then然后
直到Loc不是
 Loc。 Value =Yes
 Set Loc = .FindNext（Loc）
循环
 End If 
 End With 
 Set Loc = Nothing 
 Next 
  

I want to search all worksheets for a date, and when found replace the date by "yes".

I have the code below:

Sub Aftekenen()

Dim Sh As Worksheet
Dim Loc As Range
Dim Datum As String

Datum = "28-12-2015"

For Each Sh In ThisWorkbook.Worksheets
With Sh.UsedRange

    Set Loc = .Cells.Find(What:=Datum)

    If Not Loc Is Nothing Then
        Do Until Loc Is Nothing
            Loc.Value = "Yes"
            Set Loc = .FindNext(Loc)
        Loop
    End If
End With
Set Loc = Nothing
Next

End Sub

However the code runs, however it never reaches the For each loop. When I remove the "-" so the date will become a number it finds it. However when the dashes are present the code does not find any fitting value, however these are present.

解决方案

try this, for each ... next method with comparing .Text (not value, because of value of the cell for "28-12-2015" is 42366), it works well but some users have an objection to this method by some reasons, as for me less coding better reading

Sub Aftekenen()
    Dim Sh As Worksheet, Loc As Range, Datum As String
    Application.ScreenUpdating = 0
    Datum = "28-12-2015"
    For Each Sh In ThisWorkbook.Worksheets
        For Each Loc In Sh.UsedRange
            If Loc.Text = Datum Then Loc.Value = "Yes"
        Next Loc
    Next Sh
    Application.ScreenUpdating = 1
End Sub

update

if you prefer .find method then for searching of the dates is required to switch Application.FindFormat.NumberFormat to format of the cells with dates, due to the date in .text is 18-05-2015, but .value is 42142

below is your updated variant

Sub Aftekenen()

Dim Sh As Worksheet
Dim Loc As Range
Dim Datum As String

Application.FindFormat.NumberFormat = "dd-mm-yyyy"

Datum = "28-12-2015"

For Each Sh In ThisWorkbook.Worksheets
With Sh.UsedRange

    Set Loc = .Cells.Find(Datum, , xlValues, , , , True, , True)

    If Not Loc Is Nothing Then
        Do Until Loc Is Nothing
            Loc.Value = "Yes"
            Set Loc = .FindNext(Loc)
        Loop
    End If
End With
Set Loc = Nothing
Next

这篇关于在整个工作簿中查找日期（以字符串的形式显示）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！