How to Search in Excel via VBA Code
109.8 هزار بار بازدید -
11 سال پیش
-
In this tutorial, we will
In this tutorial, we will guide you on how to search in Excel using visual basic. Visual Basic is a simple programming language developed by Microsoft. It is also integrated into several Microsoft Office applications. By using Visual Basic, we can add advanced functions using VBA scripts called macros.
Usually macros are used to perform that needs to be carry out repeatedly. As we have a very limited functionality of Search function in Excel therefore we can use the VBA macros to enhance search results as per our requirements. VBA Excel find script can be written in many ways depending on your needs. Here we are guiding you on creating a simple search in Excel VBA code.
Script can be found at the end of the lesson.
Don't forget to check out our site http://howtech.tv/ for more free how-to videos!
ithowtovids - our feed
Facebook: howtechtv - join us on Facebook
https://plus.google.com/1034403827176... - our group in Google+
Step 1 -- Open the VBA Editor
First of all, go to the Developer tab and click on the Visual Basic option to open up the VBA editor.
Now let's double click on the "Sheet1" module, select "Worksheet" from the object drop down list and choose "Before double click" for the event handler. This would automatically enable the macro when we double click on the sheet.
Step 2 -- Assign variables and Do while loop
First of all, we will assign the variables required over here. After that, we will specify on which sheet the action is to be performed. For this tutorial, we will specify Sheet1 over here.
After that, we will use the "With" and "End With" statement, which will basically allow us to run a series of statements on an object without having to recall the object name again and again.
With that done, we will insert the "Do while" loop and add the "True" condition after that to exit the loop.
Step 3 -- Use the Input Box to assign as RetValue
Once done, we will enter the code to prompt an Input box so that the user can search for the value. For that, we will use the Input Box keyword and enter the message within quotes over here which will be displayed on the message box.
After that, we will configure the loop to be stopped if the user clicked on "Cancel" or if a blank value is entered in the field.
Step 4 -- Select the Range
Now we will set the range where the query is to be searched in and after that, we will display the cell number, which contains the search item, in the message box.
Notice here that by default, we have configured "match case" to false. If you enable this option, it would only pick out those entries whose case of each corresponding letters match with the search query.
Step 5 -- Insert a Display Message if Value is blank
Now we have to include the message which would be displayed if no results are found. For that, we will use the Prompt keyword and enter the message within quotes.
After that, we will enter the message that is to be displayed if the search query is found. This time, we will be referencing the value and the row count within the prompt keyword to display the value which was searched for and the row in which it was found.
Step 6 -- Enter the value to find in Excel Workbook
Now let's go back to the Excel workbook and double click anywhere to activate the macro.
Great! The message box appeared. Now type your query over here and hit enter. You will notice that it will find the search query and display the row number in which it was present, if the keyword in the query is present in the excel sheet.
Step 7 -- Display Message if value cannot be found
Similarly, let's enter a value which is not present in the excel file. And there you have it. An error message has been displayed here that the record was not found.
And that's basically how VBA excel find script is written and how you can search in Excel using VBA.
Script can be found here:
http://howtech.tv/wp-content/images/0...
Usually macros are used to perform that needs to be carry out repeatedly. As we have a very limited functionality of Search function in Excel therefore we can use the VBA macros to enhance search results as per our requirements. VBA Excel find script can be written in many ways depending on your needs. Here we are guiding you on creating a simple search in Excel VBA code.
Script can be found at the end of the lesson.
Don't forget to check out our site http://howtech.tv/ for more free how-to videos!
ithowtovids - our feed
Facebook: howtechtv - join us on Facebook
https://plus.google.com/1034403827176... - our group in Google+
Step 1 -- Open the VBA Editor
First of all, go to the Developer tab and click on the Visual Basic option to open up the VBA editor.
Now let's double click on the "Sheet1" module, select "Worksheet" from the object drop down list and choose "Before double click" for the event handler. This would automatically enable the macro when we double click on the sheet.
Step 2 -- Assign variables and Do while loop
First of all, we will assign the variables required over here. After that, we will specify on which sheet the action is to be performed. For this tutorial, we will specify Sheet1 over here.
After that, we will use the "With" and "End With" statement, which will basically allow us to run a series of statements on an object without having to recall the object name again and again.
With that done, we will insert the "Do while" loop and add the "True" condition after that to exit the loop.
Step 3 -- Use the Input Box to assign as RetValue
Once done, we will enter the code to prompt an Input box so that the user can search for the value. For that, we will use the Input Box keyword and enter the message within quotes over here which will be displayed on the message box.
After that, we will configure the loop to be stopped if the user clicked on "Cancel" or if a blank value is entered in the field.
Step 4 -- Select the Range
Now we will set the range where the query is to be searched in and after that, we will display the cell number, which contains the search item, in the message box.
Notice here that by default, we have configured "match case" to false. If you enable this option, it would only pick out those entries whose case of each corresponding letters match with the search query.
Step 5 -- Insert a Display Message if Value is blank
Now we have to include the message which would be displayed if no results are found. For that, we will use the Prompt keyword and enter the message within quotes.
After that, we will enter the message that is to be displayed if the search query is found. This time, we will be referencing the value and the row count within the prompt keyword to display the value which was searched for and the row in which it was found.
Step 6 -- Enter the value to find in Excel Workbook
Now let's go back to the Excel workbook and double click anywhere to activate the macro.
Great! The message box appeared. Now type your query over here and hit enter. You will notice that it will find the search query and display the row number in which it was present, if the keyword in the query is present in the excel sheet.
Step 7 -- Display Message if value cannot be found
Similarly, let's enter a value which is not present in the excel file. And there you have it. An error message has been displayed here that the record was not found.
And that's basically how VBA excel find script is written and how you can search in Excel using VBA.
Script can be found here:
http://howtech.tv/wp-content/images/0...
11 سال پیش
در تاریخ 1392/07/10 منتشر شده
است.
109,890
بـار بازدید شده