Solved

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

Posted on 2014-12-16
4
103 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 35

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 51

Accepted Solution

by:
Rgonzo1971 earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

729 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