Vba Worksheet Range Find

This example will select the first cell in the range with the inputbox value.

Vba worksheet range find. First occurrence set foundrng range a3 h19 find ms msgbox foundrng address output amp amp amp amp amp gt. In order for vba to run the macros do the tasks it needs to identify the cells on which the called tasks need to be performed. Range a1 a5 a5 a10 select selects cell a5. It searches a range of cell s containing a given value or format.

Select the range which we want to name first as follows step 2. The range object can include a single cell an entire row or column or multiple cells spread across rows columns. Sub find first dim findstring as string dim rng as range findstring inputbox enter a search value if trim findstring then with sheets sheet1 range a a set rng find what findstring after cells cells count lookin xlvalues lookat xlwhole searchorder xlbyrows searchdirection xlnext matchcase false if not rng is nothing then application goto rng true else msgbox. Expression find what after lookin lookat searchorder searchdirection matchcase matchbyte searchformat the answer should be saved as a range object so the set keyword should be used when using the find method.

Union range a1 range a10 select selects cells a1 and a10. Sub findstring dim c as range dim firstaddress as string with worksheets 1 range a1 a500 set c find abc lookin xlvalues if not c is nothing then firstaddress c address do c value replace c value abc xyz set c findnext c loop while not c is nothing end if end with end sub. Intersect range a1 a5 range a5 a10 select selects cell a5. Range a1 a10 select selects cells a1 and a10.

C 5 find the next occurrence set foundrng range a3 h19 findnext foundrng msgbox foundrng address output amp amp amp amp amp gt. The find function is a member of range. There is a name box above the range we use that to give a name to the range for this example i have named the ranged as new. The find function is very commonly used in vba.

Range b5 offset 1 3 resize 3 5 in the end add select to tell vba to select the range and when you run this code it will select the range. C 10 find the previous occurrence again set foundrng range a3 h19 findprevious foundrng msgbox foundrng address output amp amp amp amp amp gt. It is used in the format. The three most important things to know about find are.

In the context of the excel worksheet the vba range object denotes cells either single or multiple.