Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to search and find data

0

Dear Sirs,

I have researched the following macro, which starts ok, but stops when I try to set the data from 'mydata'. I suspect the use of Variant may be where my error lies. The search data is always 5 numerical characters.

The code I am trying to use is;

Sub Find_location()

    ActiveSheet.Unprotect (no password set)

    Dim Worksheet As Range

    Dim mydata As Variant

      mydata = InputBox("Enter the STD code to look for")

      With Worksheets(1).Range("C1:C615").Select

        Set mydata = .Find("mydata", LookIn:=xlWhole)

        If found Then

        MsgBox mydata("was found")

        Else

        MsgBox mydata("was not found")

      End If

    End With

    ActiveSheet.Protect

End Sub

I realise I can find the data with Ctrl+F, but as I need this information frequently, I think a macro will help me a lot.

My ultimate goal is to find the data from my C column and print the corresponding D cell contents in the MsgBox. If the data is not found, the next MsgBox displays the 'not found' result. Therefore, searching for 01733 will produce Peterborough in the MsgBox.

Thank you for looking at this for me.

Hi John,

I did not realise the file I sent was in an encrypted folder. Obvious, with hindsight.

Thank you for your help. I have changed the macro name to a more descriptive name. There is only one macro. It works well at the start, but reports the number I enter is not found. I've tried to follow your code. It looks good to me, so I cannot work out where to go next.

Thank you once again for any advice you can offer.

excelmacros-vba
MS Office Version: Office365
posted by: MrEMann (rep: 20) on Sat Nov 5, 2022 at 10:35 pm
Answer
Discuss

Discussion

Hi Again

Please see my revised answer and version of your file. For some reason the Set line following:

With Worksheets(1).Range("C1:C615")

got cropped in my original answer and file (so the code failed). All should work well now (but I realised I left my macro name- feel free to change it (but you will need to reassign the button to that newly- named macro).

For future, note that you can use the Discussion button to respond to an Answer (but any details of the probl;em should be in the original question).

If it works as expected, kindly remember to mark the Answer as Seelcted.
John_Ru (rep: 6142) Nov 8, '22 at 9:28 am
Add to Discussion

Answers

0
Selected Answer

MrEMan

REVISED ANSWER: 

I've now applied my revised macro below to the file you've attached to your question.

If you click the (new) green button, the following modifed code will run. I've highlighted key changes in bold.

Sub Find_location()

    'ActiveSheet.Unprotect '(no password set)

    Dim Found As Range

    Dim mydata As String

    ' convert user input to string
    mydata = CStr(InputBox("Enter the STD code to look for"))

    ' check 5 characters entered
    If Len(mydata) <> 5 Then
        MsgBox "Please enter a 5-digit code"
        Exit Sub
    End If

    With Worksheets(1).Range("C1:C615") ' ### do not use .Select here
      Set Found = .Find(mydata, LookIn:=xlValues, LookAt:=xlWhole)

      If Not Found Is Nothing Then

        MsgBox mydata & " = " & Found.Offset(0, 1).Value, vbOKOnly, "STD Code found:"

        Else

        MsgBox "Please check code " & mydata & " and retry", vbOKOnly, "STD Code not found:"

      End If

    End With

    'ActiveSheet.Protect

End Sub

Note that I've commented out the .Protect /Unprotect lines- you should be able to run the code from another sheet without unprotecting your data.

Hope this fixes your problem. If so, please remember to mark this answer as Selected,

posted by: John_Ru (rep: 6142) on Mon Nov 7, 2022 at 7:46 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login