Find using "LookIn:=xlFormulas" doesn't seem to work with my table

Question

How can I get this to look in hidden rows? This only works if I filterer my table every time. I was expecting "LookIn:=xlFormulas" to do the trick, but it doesn't.

Sub MarkCompleted1()
    Application.ScreenUpdating = False
    Range("Table1[[#Headers],[SO'#]]").Select
    If Range("C:C").Find(What:=Range("S1").Value, After:=ActiveCell, _
            LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False) _
            Is Nothing Then
        ActiveSheet.Range("S1").Select
        MsgBox "Sales Order # " & Range("S1") & " Not Found", _
            vbInformation, "Information"
    Else:
        Range("C:C").Find(What:=Range("S1").Value, After:=ActiveCell, _
            LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False).Activate
        MarkCompleted2
    End If
End Sub

Please define "doesn't seem to work", What have you tried? Also, check out **how to create a [mcve]** as well as "[ask]". — ashleedawg, Aug 28 '18 at 01:22
Possible duplicate of [Perform a find on hidden cells](https://stackoverflow.com/questions/6297624/perform-a-find-on-hidden-cells) — ashleedawg, Aug 28 '18 at 01:28

score 0 · Accepted Answer · answered Aug 28 '18 at 02:04

0

The worksheet's MATCH function looks in hidden rows and columns.

Sub MarkCompleted1()
    dim m as variant

    m = application.match(Range("S1").Value, columns(3), 0)

    If iserror(m) Then
        Range("S1").Select
        MsgBox "Sales Order # " & Range("S1") & " Not Found", vbInformation, "Information"
    Else
        Range("C" & m).Activate
        MarkCompleted2
    End If

End Sub

answered Aug 28 '18 at 02:04

Thanks Jeeped, however, when I use your macro I now get the message "Sales Order # [232393] Not Found" on everything I put in cell "S1" (which are 6 digit order numbers) even with no rows hidden. – Jeff Jensen Aug 28 '18 at 03:13
1

You're probably mixing up true numbers and text-that-look-like-numbers. That's probably the only way a find with xlWhole would work when match does not. – Aug 28 '18 at 03:43
That worked. You were correct about the text/numbers thing. Thanks for your help, I really appreciate it! – Jeff Jensen Aug 28 '18 at 04:45

Find using "LookIn:=xlFormulas" doesn't seem to work with my table

1 Answers1