Searching Data In Excel Sheets
One of the main tasks an Excel VBA programmer encounters is searching excel sheets for a specific value. While there are many different methods for doing a search, in this article I will only cover the basic method of using a For Next loop. In this example it will be assumed that we have a sheet with 1000 rows and 2000 columns and we are looking for the string “Target”:
As you can see the value i assumes all values between 1 and 1000 to cover the rows and the value j assumes all values between 1 and 2000 to cover the columns.
This method although it is very easy to implement, is very uneconomical. Reading and writing data to cells is one of the most resource consuming commands in VBA. In the method below the range of cells we are searching is assigned to an array. Then all the searching is done on the array rather than the cells. This way our code will work much more efficiently: