Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1374
  • Last Modified:

Find specific text in cell then copy all rows with that text

This has probably been asked in a similar way before but I cannot find the exact code I need which is:

In Sheet named 'Actual' find the first occurance of the word 'House' then copy that row and all other rows below it that contain the word 'house' in the same column, and paste these in Sheet 'Reporting'

Thanks
0
Jagwarman
Asked:
Jagwarman
1 Solution
 
NBVCCommented:
You can do this simply with formulas.

First add a helper column to the Actual sheet... in a new column, assuming column X enter a formula in row 2 like:

=IF(A2="House",COUNT(X$1:X1)+1,"")  and copy down.

replace X with the column you are putting the formula in.

Now in your summary sheet in row 2 enter formula:

=IFERROR(INDEX(Actual!A:A,MATCH(ROWS($A$2:$A2),Actual!$X:$X,0)),"")

again replacing the X with the column you put the helper formula in.

copied across the number of columns required returned, then down as far as desired.
0
 
JagwarmanAuthor Commented:
NB_VC this is part of a bigger Macro so I would prefer to have VBA code. But thanks anyway
0
 
yuppyduCommented:
Similar issue solved. Give it a look, with a little adaptation I think it should work for you as well.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28305918.html
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rgonzo1971Commented:
Hi,

pls try

With Sheets("Actual")
    Set c = .Cells.Find("House", LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
        Do
            strRange = strRange & c.Row & ":" & c.Row & ","
            Set c = .Cells.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
End If
strRange = Left(strRange, Len(strRange) - 1)
.Range(strRange).Copy
End With
Sheets("Actual").Range(strRange).Copy Sheets("Reporting").Range("A1")

Open in new window

Regards
1
 
JagwarmanAuthor Commented:
Hi Rgonzo1971

I am getting Variable not defined in
c = in ...... Set c = .Cells.Find("House", LookIn:=xlValues)


????
Regards
0
 
Saqib Husain, SyedEngineerCommented:
NO POINTS FOR THIS

Add this to the beginning of the code

dim c as range
0
 
JagwarmanAuthor Commented:
I am getting this on each of these now

DataCapture
strRange
firstAddress

so I made each one Dim xxx as Range

now I get 'Object caraible or with block variable not set at

DataCapture = c.Address
0
 
JagwarmanAuthor Commented:
ssaqibh  are you around to assist me with this please?
0
 
Rgonzo1971Commented:
Hi,

strRange
firstAddress

 should be dim as String

because I cannot see what DataCapture is

try to dim  as Variant

EDIT All Dim as String

Regards
0
 
JagwarmanAuthor Commented:
works great now. Thanks
0
 
JagwarmanAuthor Commented:
Rgonzo1971 I am happy to post as new but, is it possible to make a small amendment to make the macro look for the word in a specific column i.e. 'H'

As I say happy to post as new.

Regards
0
 
JagwarmanAuthor Commented:
Rgonzo1971 sorry one more request. When running the macro today there were no items with the specific words so it falls over at
strRange = Left(strRange, Len(strRange) - 1)

with Invalid procedure call or argument. Can you help me with this.

Thanks
0
 
JagwarmanAuthor Commented:
I have one more request

WS.Range(RowFM & ":" & RowTO).EntireRow.Copy WSCopy.Cells(MaxRowCopy, "A") is returning an error

"Excel cannot complete the task with available resources..........."

When I hove over the code 'RoeTO it is trying to go 1048576 I think this is because there is actually no data to copy ?????

Can you help?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now