Copying/searching with a variable range!!

**anujsethi91** · 05-29-2013, 06:17 AM

Hi, i have a sheet in which i want to find all occurrences of a string suppose "MSC6", later i want to find another string suppose "Fault" within the range of "MSC6",ie its first and last occurrece.(PS. MSC6 not necessarily be isolated, ie the string might be "sdgsgMSC6dfsdff", this should also be considered).
also if you could let me know how to copy all the data between the first occurrence and the last occurrence + 5 lines of "MSC6" . Thanks

**ragulduy** · 05-29-2013, 07:36 AM

I think this will kind of do what you want, although I'm not sure what the +5 lines bit refers to.

Please Login or Register  to view this content.

**anujsethi91** · 05-30-2013, 02:06 AM

Hi, the +5 line means that i don't want the search to end at the final MSC6,, i want it to search till after 5 lines of the last occurrence of MSC6. hey and can you guide me through this code of yours, i don't seem to get it. Thanks

**ragulduy** · 05-30-2013, 03:56 AM

Ok, so the first step is to assing the string you want to look at to the variable MSC6, I made something up as an example.

Then I use Instr to find the first occurance of "MSC6" in the string assinged to the variable. The arguements for Instr are the character to start looking at, the string to look in, and the string to look for.

I then use Len to find the length of the string and loop through it backwards, so I start looking for "MSC6" using Instr but with the character to start looking at being the last character of the string, and moving back one character at a time until it finds it, which gives me the last occurence.

Then I use Instr to find "fault", starting at the location of the first occurance of MSC6, and looking ni the string but using left() to only look at the first so many characters, defined by the last occurance of MSC6.

Finally I use mid() to copy the middle section of the string between the first and last occurences of MSC6.