Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

find specific text in row one then copy from that cell down

Posted on 2014-12-16
4
Medium Priority
?
105 Views
Last Modified: 2014-12-16
I have searched EE but cannot find the specific code I need. Would appreciate the help of an expert.

My specific need is to find the word 'book' in column 'B' then move 6 to the right then copy all cells from that cell down to the last cell that contains data.

Many thanks
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 36

Expert Comment

by:Kimputer
ID: 40502130
Does it have to be "book", or also when it's part of that cell like "abcbookdef" ?
Does "down to the last cell that contains data" mean that if "book" is found in B3, and column B runs through B100, that H3 should be filled through H100 (copied from B3:B100)?
Or did you mean Only fill H4 when the next "book" is encountered in column B?
0
 

Author Comment

by:Jagwarman
ID: 40502140
Hi kimputer,

in this instance it will be Book, so, if 'Book' is found in B3 I need to move 6 to the right and then copy from H3 to H100

If column B runs through B100, then I need to copy H3 through to H100 I will then Paste this data in column A in Sheet2 cell A2
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40502148
Hi,

pls try

Sub Macro2()

Set Rng = ActiveSheet.Range("B:B").Find(What:="Book", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not Rng Is Nothing Then
    BookRow = Rng.Row
    Range(Range("H" & BookRow), Cells(Range("B" & Rows.Count).End(xlUp).Row, "H")).Copy Destination:=Sheets("Sheet2").Range("A2")
End If
End Sub

Open in new window

Regards
0
 

Author Closing Comment

by:Jagwarman
ID: 40502263
Thanks Rgonzo exactly what I was looking for
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

704 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