Using the Find Function and Multiple Offset Columns

**ashleys.nl** · 09-08-2011, 12:34 PM

Hey Everyone,

I'm kinda of new to VB and you have all been so helpful. Currently I've been spending the paste little while writing a VB Excel Code that will search a worksheet for specific text ("cat") , once it finds the location of said text it copies the next cell over [Offset(, 1)] into another worksheet (Sheet2). Anywho in my actual code once I find the row I'm looking for I have to copy several columns from that row. So, I'm just curious is there a more efficient way to do this than to use the "Find" function every time? I mean I already know the row I'm looking for, so should I do the entire line of code again just to change the offset to (, 2). and again and again?

Below is the sample code I'm working with

Please Login or Register  to view this content.

Thanks again everyone for your help!

**ashleys.nl** · 09-08-2011, 12:35 PM

Also, how do I look through the whole worksheet not just after the ActiveCell?

Thanks again

**StephenR** · 09-08-2011, 12:40 PM

A better way is to assign a range variable to the Find and then you can also check the value is found and avoid an error if not:

Please Login or Register  to view this content.

**ashleys.nl** · 09-08-2011, 12:52 PM

Hey Stephen,

That worked great, except I had to change "rfind.Offset...." to "r.Offset". "rfind" wasn't an object. But other than that it works great! thank you soo much for all your help lately. Below is the code I'm using now for others to see.

Please Login or Register  to view this content.

**ashleys.nl** · 09-08-2011, 01:14 PM

Hey, Just when I think I have something solved I have more work to do....

So when I try to implement this into my master work book, I realize that my search term "Cat" appears 3 times and at each occurance there is useful information in that row that I want to take away. Is there a way to implement the code so that I could have multiple "r" values of "r1", "r2", and "r3"? Each value of course refering to their respective order of occurence.

Thanks again in advance

**ashleys.nl** · 09-08-2011, 01:27 PM

Originally Posted by ashleys.nl

Hey, Just when I think I have something solved I have more work to do....

So when I try to implement this into my master work book, I realize that my search term "Cat" appears 3 times and at each occurance there is useful information in that row that I want to take away. Is there a way to implement the code so that I could have multiple "r" values of "r1", "r2", and "r3"? Each value of course refering to their respective order of occurence.

Thanks again in advance

Currently I'm using this code , but with no luck. It finds the first occurence of "cat" twice rather than moving on to the next.

any thoughts???

Please Login or Register  to view this content.

**StephenR** · 09-08-2011, 02:01 PM

Try this:

Please Login or Register  to view this content.

Using the Find Function and Multiple Offset Columns

LinkBack

Thread Tools

Rate This Thread

Display

Using the Find Function and Multiple Offset Columns

Re: Using the Find Function and Multiple Offset Columns

Re: Using the Find Function and Multiple Offset Columns

Re: Using the Find Function and Multiple Offset Columns

Re: Using the Find Function and Multiple Offset Columns

Re: Using the Find Function and Multiple Offset Columns

Re: Using the Find Function and Multiple Offset Columns

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions