Solved

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

Posted on 2013-12-03
13
827 Views
Last Modified: 2013-12-09
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
Comment
Question by:Jagwarman
13 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39692406
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
 

Author Comment

by:Jagwarman
ID: 39692411
NB_VC this is part of a bigger Macro so I would prefer to have VBA code. But thanks anyway
0
 
LVL 4

Expert Comment

by:yuppydu
ID: 39692427
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
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39692456
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
0
 

Author Comment

by:Jagwarman
ID: 39692723
Hi Rgonzo1971

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


????
Regards
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39692785
NO POINTS FOR THIS

Add this to the beginning of the code

dim c as range
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Jagwarman
ID: 39692841
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
 

Author Comment

by:Jagwarman
ID: 39697740
ssaqibh  are you around to assist me with this please?
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39697757
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
 

Author Closing Comment

by:Jagwarman
ID: 39698915
works great now. Thanks
0
 

Author Comment

by:Jagwarman
ID: 39705596
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
 

Author Comment

by:Jagwarman
ID: 39705630
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
 

Author Comment

by:Jagwarman
ID: 39705828
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now