在另一个工作表链接的单元格中查找值

2 回答

• 1

  好吧， .Find() 没有找到任何东西，这就是原因 . 你知道你在找什么吗？如果你写 CDate(ComboBox1.Value) 它会得到预期值吗？

  一般来说，为避免在 .Find() 找不到任何内容时出现此错误，这是解决方法：

  If Not colRng Is Nothing Then
      MsgBox "colRng is: " + colRng.Address
  End If
  

  回复于 2024-04-28T04:58:14+08:00
• 1

  所以日期在Excel中很难处理，因为它们可以被解释为整数或字符串（并使用不同的时区格式化）

  为了便于修复，以下代码应允许取消注释一行以查找相应的日期或字符串

  Private Sub TestSub()
      Dim colRng As Range
  
      With Worksheets("ws1")
          sFind = ComboBox1.Value
          'Set colRng = .Range("A1:FZ200").Find(CDate(sFind), LookIn:=xlValues)
          'Set colRng = .Range("A1:FZ200").Find(Format(CDate(sFind), "dd/mm/yyyy"), LookIn:=xlValues)
      End With
      If Not colRng Is Nothing Then Debug.Print "colRng is: " + colRng.Address
  
  End Sub
  

  作为更完整的答案，您可以使用以下通用FindAll函数...

  Private Sub FindAllDates()
      Dim Rng1 As Range, Rng2 As Range, AllRng As Range, sFind As String
  
      With Worksheets("ws1")
          sFind = ComboBox1.Value
          Set Rng1 = FindAll(CDate(sFind), .Range("A1:FZ200"), LookIn:=xlValues)
          Set Rng2 = FindAll(Format(CDate(sFind), "dd/mm/yyyy"), .Range("A1:FZ200"), LookIn:=xlValues)
      End With
      Set AllRng = CombineRange(Rng1, Rng2)
      If Not AllRng Is Nothing Then Debug.Print "AllRng is: " + AllRng.Address
  
  End Sub
  
  Function CombineRange(R1 As Range, R2 As Range) As Range
      On Error Resume Next
      Set CombineRange = R1
      If Not R2 Is Nothing Then Set CombineRange = Application.Union(R1, R2)
      If CombineRange Is Nothing Then Set CombineRange = R2
  End Function
  
  Function FindAll(What, _
      Optional SearchWhat As Variant, _
      Optional LookIn, _
      Optional LookAt, _
      Optional SearchOrder, _
      Optional SearchDirection As XlSearchDirection = xlNext, _
      Optional MatchCase As Boolean = False, _
      Optional MatchByte, _
      Optional SearchFormat) As Range
  
      'LookIn can be xlValues or xlFormulas, _
       LookAt can be xlWhole or xlPart, _
       SearchOrder can be xlByRows or xlByColumns, _
       SearchDirection can be xlNext, xlPrevious, _
       MatchCase, MatchByte, and SearchFormat can be True or False. _
       Before using SearchFormat = True, specify the appropriate settings for the Application.FindFormat _
       object; e.g. Application.FindFormat.NumberFormat = "General;-General;""-"""
  
      Dim SrcRange As Range
      If IsMissing(SearchWhat) Then
          Set SrcRange = ActiveSheet.UsedRange
      ElseIf TypeOf SearchWhat Is Range Then
          Set SrcRange = IIf(SearchWhat.Cells.Count = 1, SearchWhat.Parent.UsedRange, SearchWhat)
      ElseIf TypeOf SearchWhat Is Worksheet Then
          Set SrcRange = SearchWhat.UsedRange
      Else: SrcRange = ActiveSheet.UsedRange
      End If
      If SrcRange Is Nothing Then Exit Function
  
      'get the first matching cell in the range first
      With SrcRange.Areas(SrcRange.Areas.Count)
          Dim FirstCell As Range: Set FirstCell = .Cells(.Cells.Count)
      End With
  
      Dim CurrRange As Range: Set CurrRange = SrcRange.Find(What:=What, After:=FirstCell, LookIn:=LookIn, LookAt:=LookAt, _
          SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)
  
      If Not CurrRange Is Nothing Then
          Set FindAll = CurrRange
          Do
              Set CurrRange = SrcRange.Find(What:=What, After:=CurrRange, LookIn:=LookIn, LookAt:=LookAt, _
              SearchDirection:=SearchDirection, MatchCase:=MatchCase, MatchByte:=MatchByte, SearchFormat:=SearchFormat)
              If CurrRange Is Nothing Then Exit Do
              If Application.Intersect(FindAll, CurrRange) Is Nothing Then
                  Set FindAll = Application.Union(FindAll, CurrRange)
              Else: Exit Do
              End If
          Loop
      End If
  End Function
  

  回复于 2024-04-28T04:58:14+08:00