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:

• VBA, Working With Arrays.

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