VBA Cannot find in if cells are hidden. Even if ,xlformulas is used.

[pedie]

Hi, below is the currentcode. It does not find if cells is hidden. How do i code to find it in hidden how.
I want to find exact match.
Thanks.

[/FONT]

[FONT=Courier New]Dim Found As Range, LR As Long, x As String
x = range("B1").value[/FONT]
[FONT=Courier New]With activesheet[/FONT]
[FONT=Courier New]    LR = .Range("E" & Rows.Count).End(xlUp).Row
      Set Found = .Range("E6:E" & LR).Find(what:=x, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
      If Not Found Is Nothing Then[/FONT]
[FONT=Courier New]          msgbox "kkkk"
      End If
End With
End Sub

 

[taigovinda]

It works for me when changing xlValues to xlFormulas... Did you:

1. Have it work.
2. Hide the row and it doesn't work.
3. Change to xlFormulas and it *still* doesn't work?

It seems that since this is known to work when using xlFormulas, there must be some other issue at play.

 

[pedie]

Tried with xlformulas too but wont work.
Entire e col is hidden.

Thanks again

[/FONT]
[FONT=Courier New]LR1 = .Range("E" & Rows.Count).End(xlUp).Row
      Set Found = .Range("E6:E" & LR1).Find(what:=x, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

 

[pdpbeethoven]

try looping through the cells?

i tried hiding the column and xlFormulas still works for me.

 

[pedie]

I want it to be in a fastest way. I already have lots in my code execution process so thought loop might make the code execution even slower...

But I think there is a way out.

Thanks

 

[VoG]

Pedie

• application.screenupdating =false
• unhide the column
• search & find the value
• do something
• hide the column
• turn screen updating back on

 

[pedie]

Yes ok. Thanks for suggestion Peter.
Thanks

 

[ZVI]

Hi,
Here is another approach:

Sub Test()
  Dim x As Range
  Set x = EvalFind([B1], [E:E]) ' or the same: Set x = EvalFind(Range("B1"), Range("E:E"))
  If x Is Nothing Then
    MsgBox [B1] & " is not found"
  Else
    MsgBox [B1] & " is found in " & x.Address
  End If
End Sub

Function EvalFind(What, Where As Range) As Range
  Dim i&, q$
  If VarType(What) = vbString Then q = """"
  On Error Resume Next
  i = Evaluate("MATCH(" & q & What & q & "," & Where.Address(External:=True) & ",0)")
  If i > 0 Then Set EvalFind = Where(i)
End Function

Regards,

 

[ZVI]

And the similar:

Sub Test1()
  Dim x As Range
  Set x = WsFuncMatch([B1], [E:E]) ' or the same: Set x = WsFuncMatch(Range("B1"), Range("E:E"))
  If x Is Nothing Then
    MsgBox [B1] & " is not found"
  Else
    MsgBox [B1] & " is found in " & x.Address
  End If
End Sub

Function WsFuncMatch(What, Where As Range) As Range
  Dim i&
  On Error Resume Next
  i = WorksheetFunction.Match(What, Where, 0)
  If i Then Set WsFuncMatch = Where(i)
End Function