code to find text, offset 1 column and paste to new workbook not working

**trillium** · 07-09-2011, 12:36 AM

Hi there

I'm working with a report that has text and many empty cells. However, periodically the word "Location" appears in column A. I would like to copy what appears next to that word (column B) and paste it in the next empty cell on another sheet under the word "Location".

Basically, I am searching a report output and trying to make a Database summary of the information onto another tab to make it easier to read, etc.

I'm struggling with trying to find a match for text in a column though...

Please Login or Register  to view this content.

I highlighted in Red where the marco bugs and gives a Runtime 424: Object required error, which I can't seem to solve.

OK, in the preview it doesn't show. This is the line it stops at:

Please Login or Register  to view this content.

I will be going through other columns to capture the other info and paste it as well.

Thoughts?

**JBeaucaire** · 07-09-2011, 01:39 AM

Your code fails because of the .Select on the end, try removing that.

If all you're trying to do is copy all the column J values to the new sheet for the rows that have "Location" in column A, this is what AUTOFILTER was invented for. This method will filter the first sheet leaving only the rows with "Location" showing, then copy the column J values all at once to the Sheet2 in cell A2. No looping.

Please Login or Register  to view this content.

**trillium** · 07-09-2011, 08:20 AM

Hi there!

I'd thought about autofilter, but the information on the report output page is not in a chart form, so there are lots of empty spaces inbetween the text. The other titles I want to use are also scattered around on the sheet. Luckily each output has the information always in the same column, so for example, "Location" is always column "A", another one is "J" etc. But above and below the target text, there are empty spaces, autofilter won't work. This way why I was trying to use a loop instead.

:-)

**snb** · 07-09-2011, 08:32 AM

Please Login or Register  to view this content.

**trillium** · 07-09-2011, 06:29 PM

Hi again... autofilter does not work because of all the empty spaces in the column, it just finds blanks underneath... is there any way a loop through the column can work?