Finding a value within cells linked from another worksheet

Question

Today I have a new problem with the .Find function. I am trying to do .Find through a worksheet that has cells linked from another sheet. I have ws1 which contains a header row of linked dates from ws2. As an example: ws1 A1 cell formula is =ws2!$A$1, ws1 B1 formula is =ws2!$A$2. ws2 contains dates in column A.

I am using .Find in ws1 to find the value displayed within ComboBox1 on a userform(i.e. ComboBox1.Value). ComboBox1 source is linked directly to ws2 A column and the value on update is changed to a dd-mmm-yy format.

Here is the code:

Private Sub TestSub()
Dim colRng As Range

With Worksheets("ws1").Range("A1:FZ200")
    Set colRng = .Find(CDate(ComboBox1.Value))
End With
MsgBox "colRng is: " + colRng.Address

End Sub

This gives me Run Time Error 91: Object or With Block variable not set. On Debug the value of colRng is Nothing and not a Range.

The only thing I can think of is that the linked cells are messing up the formatting but I am not sure how to fix that issue. Any ideas?

UPDATE: I tested the .Find function in my statement with a string which does work.

With Worksheets("ws1").Range("A1:FZ200")
    Set colRng = .Find(ComboBox1.Value)
End With
MsgBox "colRng is: " + colRng.Address

I do not know how to go about finding the value within a cell if it is linked from another sheet however. Any suggestions?

Answer 1

Well, the .Find() has not found anything and this is the reason. Do you know what you are looking for? If you write CDate(ComboBox1.Value) does it get the expected value?

In general, to avoid this error in case that .Find() has not found anything, this is the work around:

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

Answer 2

So dates can be hard to deal with in Excel as they can be construed as integers or strings (and formatted with different timezones)

For an easy fix the following code should allow one line to be uncommented to find the respective dates or strings

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

As a more complete answer, you could use the following generic FindAll function...

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