CODEWITHSUNDEEP
vba
Leandro Moreira
Leandro Moreira

Reputation: 215

Find a range in another Sheet

I have the piece of code below:

       Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate
 Dim cl As Range, rTest As Range 

    Set rTest = Range("a1", Range("a1").End(xlToRight)) 
    For Each cl In rTest 
        If Not cl.Value > 0 Then 
            cl.EntireColumn.Hidden = True 
        End If 
    Next cl 
End Sub

Where says What=4, i would like to search the Range(e15) of another worksheet. Search the value of E15 in one sheet and look for it in a specific range in another sheet. I have all the other piece set, but I dont know how I can reference the value of e15, this can 4 or any other number. After finding, hide all columns that are not my specific value. Many thanks!

Upvotes: 0

Views: 383

Answers (1)

user3598756
user3598756

Reputation: 29421

you should act like follows

Dim f As Range

Set f = Selection.Find(What:=Worksheets("otherWorksheetName").Range("e15").Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
If Not f Is Nothing Then
    f.Activate '<--| what do you need this for?

    Range("A1", Range("A1").End(xlToRight)).EntireColumn.Hidden = True '<--| hide all columns in wanted range
    f.EntireColumn.Hidden = True '<--| unhide found range column
End If

where you have to change "otherWorksheetName" to you actual "other " worksheet name

Upvotes: 2

Related Questions