Solved

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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

623 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