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”:
Dim i As Integer
Dim j As Integer
For i = 1 To 1000
For j = 1 To 2000
If Cells(i, j) = "Target" Then
'your code here
End If
Next i
Next j
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:
Dim i As Integer
Dim j As Integer
Dim arrData2Search() As Variant
Set arrData2Search = Range(Cells(1, 1), Cells(1000, 2000)).Value
For j = 1 To 1000
For i = 1 To 2000
If arrData2Search(i, j) = "Target" Then
'Your code here
End If
Next i
Next j
See Also:
If you need assitance with your code, or you are looking for a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com