none
Cells.Find Not returning a value RRS feed

  • Question

  • Hi,

    I am designing an excel macro wherein I need to find a certain value and then proceed with calculation as required,  for e.g.:

    Column A

    rul_abc

    rul_abd

    rul_ade

    rul_abf

    Now I am using Find function in the code to check if a value, say rul_abc is present, and if I find the value in column A then i need to proceed with different logic.

    I have used the below code to find the value:

    Sheets(1).Cells(1, 2).Activate

    Set cell = Cells.Find(What:="rul_abc", After:=ActiveCell, LookIn:=xlFormulas _
                        , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)

    Although the value as mentioned in Column A is present in 'What' parameter of Find function, the Cells.Find function does not return any range and the variable 'cell' contains 'Nothing'.

    Any help is highly appreciated.

    Kind Regards,

    Amit

    Friday, April 25, 2014 12:04 PM

All replies

  • Does it make a difference if you use LookIn:=xlValues instead of LookIn:=xlFormulas ?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 25, 2014 12:33 PM
  • Sorry, I tried that as well, but its not helping :(

    I am surprised, I have never faced such an issue.

    Friday, April 25, 2014 12:53 PM
  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 25, 2014 1:10 PM
  • The code works for me in Excel 2010 SP2. You have defined cell as a range?

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, April 25, 2014 9:00 PM
  • Are you sure that you have the correct worksheet activated.

    Sheets(1).Cells(1, 2).Activate     'This code References the first worksheet tab on the left irrespective of its name and if the user rearranges the worksheet tabs then sheets(1) reference changes to the first sheet on the left.

    You might be better to reference the worksheet by its name as follows

    Application.Goto Worksheets("Sheet1").Cells(1, 2)

    The above code line selects the worksheet and the specified cell in one line of code.

    It is not good programming to reference worksheets by their index number because users can move worksheets around. In fact, the best way to reference a worksheet is by the CodeName. The Code name can be found in  the Project Explorer which is the column on the left in the VBA editor. (If the Project Explorer is not displayed then select menu item View -> Project Explorer.) The CodeName is the one NOT in parenthesis. The name given by the user is the name in parenthesis.) The CodeName can only be changed from within the Project Explorer properties so it is not as likely that a user will change its name. Following code using the Code name.

    Application.Goto Sheet1.Cells(1, 2)


    Regards, OssieMac

    Monday, April 28, 2014 10:49 AM
  • Thanks All, for your time and interest in looking at this problem, although I could not get the root cause of this issue, I was able to solve by 1 traditional technique, i created another Macro enabled sheet and copy pasted the whole code in it and it was working.  Don't know what must have caused the issue in first place.

    Monday, April 28, 2014 2:58 PM