Cells.Find not working properly

[debauch]

Hi,
I am trying to copy a range from one sheet in a workbook, and find it in another sheet in the sameworkbook. THis is the code I have :

Private Sub commandbutton2_click()
 'copy k14
    Sheets("Attrition").Select
    Range("k14").Select
    Selection.Copy
Sheets("ActiveUsers").Select 'select new sheet
''find range k14
    Cells.Find(What:=Range("k14"), After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
end sub

The very last line is casuing the problem, 'active range method failed'

It copies fine, switches sheets, then I run into this error. It is not the 'range("k14") either, becasue it was previous a number that should match, that also was getting same error.

Please can someone help/suggust ? Maybe an easier way to find data ?

[Jacob Hilderbrand]

Why are you copying? Try something like this.

Dim Cel             As Range
Sheets("ActiveUsers").Activate
    Set Cel = Cells.Find(What:=Sheets("Attrition").Range("k14").Text, LookIn:=xlValues, _
        LookAt:=xlPart, MatchCase:=False)
    If Cel Is Nothing Then
         'No Match
    Else
         'Match
        Cel.Select
    End If

I am assuming you want to match the value/text in K14. If you just want to select the range then.

Range("K14").Select

And of course there is no need to select anything, unless you actually want to see it selected.

For Example:

Range("K14").Value = "BlahBlahBlah"

Or if the sheet you want is not active:

Sheets("MySheet").Range("K14").Value = "BlahBlahBlah"

[debauch]

Thanks for such a promp response. I am still recieving 'select method of range class failed' run-time '1004'

k14 does exist in active users, it is a 9 digit #, even if I replace k14 w/ the 9 digit #, same error. Any hints Jake ?

the error is happening at :

Else
     'Match
    Cel.Select  <<<<<<<<<<<<
End If

On the active users tab, up top in the Fx textbox, it shows the emp # I am trying to search, but it does not follow through.

[Jacob Hilderbrand]

Can you attach the file?

[debauch]

Only if I wanna get fired, lol. Let me put a mock sheet together, and I will upload shortly, thanks.

[debauch]

Ok, I have created a mocksheet. The actual workbook itself is 5.5 mb, it is very large, and I manipulate large amounts of data, and this will help kill at least an hour off my shift, to get more work done. I really appreciate all the help so far.

[debauch]

Please ?

[Bob Phillips]

Please ?

You cannot select a cell on another sheet, so you need to activate it first

Worksheets("Attrition").Activate

before the Cel.Select

[debauch]

Thanks, but if you would look at the file, I am not trying to locate the # I am copying. I am trying to get it to find the # on the attrition tab, and match it on the active users tab.

I took what you said "Worksheets("Attrition").Activate"
changed "attrition" to "activeusers" <-- that is what I am trying to find
and it still failed : "select method of range class failed"

The way you wrote it down, find the exact nubmer I am using to perform the search/find.

this is opening a whole new can of worms. but do you think if I copy the #, then siwtch sheet, and then vlookup the # it may work ? Just a thought ...

This is killin' me....assitence needed!

[mdmackillop]

Try the following. Note: LookAt changed to xlWhole and LookIn to xlValues to suit your apparent data.

Private Sub CommandButton1_Click()
    Dim ToFind As String
    ToFind = Sheets("Attrition").Range("k14")
Sheets("ActiveUsers").Activate 'Activate new sheet
On Error GoTo NotFound
    Sheets("ActiveUsers").Cells.Find(What:=ToFind, After:=[A1], LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    Exit Sub
NotFound:
    MsgBox "Data not found"
End Sub

[debauch]

YES SIR! your the man...

[mdmackillop]

Hi,
Here are 3 methods. The first 2 are similar, the third gets the Found range, which lets you manipulate data more easily using the row number of the Found cell

Sheets("ActiveUsers").Cells.Find(What:=ToFind, After:=[A1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(0, 35).Activate

Sheets("ActiveUsers").Cells.Find(What:=ToFind, After:=[A1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(0, 35).Select

Dim Found As Range
Set Found = Sheets("ActiveUsers").Cells.Find(What:=ToFind, After:=[A1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
ActiveSheet.Cells(Found.Row(), "AL").Select

[debauch]

Awsome, life is good. THis will make 1/8 of my job painless. Thanks.

P.S - I could not get the offset to work before becasue of the "exit sub" after error handling, mistake on my part.