PDA

View Full Version : [SOLVED] Cells.Find not working properly



debauch
08-23-2005, 06:48 PM
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
08-23-2005, 06:59 PM
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
08-23-2005, 07:25 PM
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
08-24-2005, 06:32 PM
Can you attach the file?

debauch
08-25-2005, 09:38 AM
Only if I wanna get fired, lol. Let me put a mock sheet together, and I will upload shortly, thanks.

debauch
08-25-2005, 09:55 AM
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
08-26-2005, 10:06 AM
Please ?

Bob Phillips
08-26-2005, 11:19 AM
Please ?

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


Worksheets("Attrition").Activate


before the Cel.Select

debauch
08-26-2005, 12:26 PM
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
08-27-2005, 06:21 AM
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
08-27-2005, 07:55 AM
YES SIR! your the man...

mdmackillop
08-27-2005, 09:09 AM
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
08-27-2005, 09:58 AM
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.