How to use Find and replace function in VBA

Today I'm going to explain you how to use another very important excel sheet function in VBA. It is find and replace function. If you are an excel user, surely you may have use this function manually. But today I'm going to explain you how to use this excel sheet function in a VBA program/macro.

In Excel 2013, you can find this function (Find & Select) under "Editing" section of "Home" Tab. If you are using any other version, you will able to find it in a similar place.

If you click on the arrow head, this kind of dropdown list will appear.

Then click on the "replace". Then you will get this pop up window.

You may have use this window before to find and replace words in your excel sheets. But today I'm going to explain you how to use this function in a VBA program. Let's use below sample paragraph to test our code.

So let's try to replace word "excel" with word "Word".

Following is the equivalent VBA code to do the "find and replace" worksheet function.

Sub FindReplace() Dim SearchText As String Dim ReplacementText As String SearchText = "excel" ReplacementText = "Word" Worksheets("Sheet1").Cells.Replace What:=SearchText, Replacement:=ReplacementText, LookAt:=xlPart, MatchCase:=False End Sub

You will get this result after running the above code.

However there is a one small problem with above code. Consider the following example.

If we run the macro for above sheet, You will notice that word "excellent" will change to "Wordlent".

Because of that, this find and replace code has limitations. If you need to avoid above type of errors, then you will need advance solution than this.

How to scroll a web page number of pixels

In this post, I'm going to explain how to scroll a web page number of pixels you need. There are various types of websites. Lot's of these websites have an option to search for information. Normally there is a text box to input search word or phrase and then there is a "search" button to click. When we search for something in a website, some websites list the results in number of pages. So we need to go to each page to find the information.

But some websites do not show results in several pages. Instead they show all the results in one page. But if there are lots of results then these websites only load small part of the complete result. They loads the other data part by part while we scroll through the page.

So if we need to write a program to get data automatically from a website where results are shown in different pages, we can do that by commanding the program to navigate to each page. For some websites we can do this by just changing the number in the address of page.

But how we collect the data from a website which loads the data only when we scroll. Here is the solution. We can write a code to scroll a web page number of pixels. You can use below code for that. It will scroll a page 100 pixels.

'Scroll web page 100 pixels down objIE.Document.parentWindow.scrollBy 0, 100

But there will be a limit of pixels you can scroll at once. For an example if you command the page to scroll for 100000 pixels, it won't do that. It will only scroll a amount, what  it normally scroll per one time when do manually. However if you want to scroll long range, you can do that by commanding the page to scroll several times like this.

'Scroll the page 10 times For i = 1 To 10     objIE.Document.parentWindow.scrollBy 0, 100000 * i     Application.Wait (Now + TimeValue("0:00:03")) Next i

Please note that objIE in above examples refers to "InternetExplorer.Application" object.