Suppose you have an excel file with thousands of rows and you want to look for a particular value. The simple approach of using a for loop to iterate through the rows and look for the value is ineffective in this case. How nice it would be to simulate “Ctrl + F” through code ? Here’s how to do this :-
The following code will look for “QTP” in the 1st column and replace all occurences with “UFT” :
Set c = objsheet.Range("A1:A500").Find("QTP") 'Find the string "QTP" If Not c Is Nothing Then firstAddress = c.Address Do 'Keep finding till values are found c.Value = "UFT" 'Replace "QTP" with "UFT" Set c =objsheet.Range("A1:A500").FindNext(c) 'Find next value Loop While Not c Is Nothing And c.Address <> firstAddress End If
Happy Automating !
Categories: MSOffice Automation