MSOffice Automation

Automating Microsoft Excel Part 3 – Using Find command through code

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 !

Harshit Kohli

Advertisements

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s