trouble with 'cells.find' returning error

Hi,

I am wondering if anyone has a solution to my problem. I am trying to set up a command button in the "FORMS" sheet called "Add new employee" that runs a form and macro to add employees to the "Station Capacity" sheet. I have attached the Excel Spreadsheet that I am working on, but the trouble bit of code that is producing the error is given below.

Cells.find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

I would appreciate any help with fixing this.

The funny thing is that when you run the macro "moveest" separately the code works. But when added to the command button "Add new employee" it doesn't.

Thanks so much.
Cheers,
Laura

Thanks for the quick response. I really appreciate your help. However I want the "What:=" value to find sheets("FORMS").Range("L2").Value in worksheet("Station Capacity"). Is that possible?

to give further background too, the value in 'sheets("FORMS").Range("L2").Value will be varied so don't wish to search for a fixed value i.e.

Cells.find(What:=Adelaide, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Instead I want something like this

Cells.find(What:=sheets("FORMS").Range("L2").Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

However, this brings up the Run-time error '1004' Activate method of Range class failed. But when I just run the macro on its own as follows:

Sub Test()

Sheets("Station Capacity").Select
Cells.find(What:=Sheets("FORMS").Range("L2").Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

The macro works and no error comes up! Very frustrating.

Thanks in advance for your help

It will be in Column A of the "Station Capacity" sheets

Thanks very much that is working! Really appreciate all your help and sorry about the lack of code tags

Please Login or Register  to view this content.

. Cheers.