Solved

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

Posted on 2014-12-16
4
95 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
  • 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 49

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

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

910 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

25 Experts available now in Live!

Get 1:1 Help Now