Vba To Look for a word in a Row and Copy and paste the cells below?

[tonywatsonhelp]

Hi Everyone,

OK This one is hopefully a little more straight forward,

I'm looking for a macro to got to Sheet1 Row 10 And look for the Cells that contain the Name "David" (These will be 7 Cells all toghther if this makes it easier you can just look for the first cell containing "David"

Now all the data id held in Rows 11 to 21 so all I need is a macro that can find the word "David" in row 10 Copy the Cells That are 10 Down and 7 across and paste values into sheet2 cell A1 to G10

Again having big problems as I don't know how to find cells in row 10 that contain the name "David" I really only need to find the first name as I say the data I want to copy will always be From the Word David One Row down So Row 11 to row 21 and 7 columns across including the column that contain the name "David"

So if Cell A10 = "David" then Cells A11:G21 copy paste to Sheet2 A1:G10
However if it was cell H10= "David" then Cells H11:N21 copy paste to Sheet2 A1:G10

and so on.

please help if you can

Thanks

Tony

 

[My Aswer Is This]

Does the cell have only the word "David"?
Or is "David" just a word in the cell Like "My sons name is David"

 

[tonywatsonhelp]

Hi My Answer is,
The cell will only contain the word "David" its basically the header with all Davids data under it so the however the row might contain blanks before we get to the name "David" "David" is only there in his own group so effectively the word "David is in 7 columns all next to each other but could be anywhere in row 10. (I can change it so "David" only aprears once in the row if its easier then I just need from "David" 7 columns across and 10 rows down.

thanks

 

[My Aswer Is This]

Try this:

Sub Look_For_David()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastcol As Long
Lastcol = Cells(10, Columns.Count).End(xlToLeft).Column
For i = 1 To Lastcol
If Cells(10, i).Value = "David" Then
Sheets("Sheet2").Range("A1:G10").Value = Cells(10, i).Offset(, 1).Resize(10, 7).Value
Application.ScreenUpdating = True
Exit Sub
End If
Next
End Sub

 

[Rick Rothstein]

Here is another macro for you to try (it uses no loops)...

[SIZE=1]Sub LookForDavid()
  Dim NameCell As Range
  Set NameCell = Rows(10).Find("David", Cells(10, Columns.Count), , xlPart, , xlNext)
  If Not NameCell Is Nothing Then Sheets("Sheet2").Range("A1:G10").Value = NameCell.Resize(10, 7).Value
End Sub[/SIZE]

 

[Trebor76]

This has been virtually duplicated here which I've wasted my time answering!!

 

[My Aswer Is This]

Thanks for the other way Rick. I wonder myself how much quicker a script performs without loops. I do my best helping people here but I'm not as good as you for sure.

Here is another macro for you to try (it uses no loops)...

[SIZE=1]Sub LookForDavid()
  Dim NameCell As Range
  Set NameCell = Rows(10).Find("David", Cells(10, Columns.Count), , xlPart, , xlNext)
  If Not NameCell Is Nothing Then Sheets("Sheet2").Range("A1:G10").Value = NameCell.Resize(10, 7).Value
End Sub[/SIZE]

 

[Rick Rothstein]

This has been virtually duplicated here which I've wasted my time answering!!

@tonywatsonhelp

Tony, you have been posting in this forum for quite awhile now and should know better... do not post the same question more than once. You may not see this as a problem, but those of us who volunteer answering questions here do see it as a problem. You can't imagine how annoying it is for a volunteer to read a question, research background material, test sample code and then formulate and post an answer to the original question only to go to another message and find it is the question he/she just answered was already answered there either with the same solution or a better one. Do you know what that ultimately means... that volunteer wasted his time answering your question when it was no longer needed thus robbing him/her of using that time to help another person who like you was looking for help. Another reason not to post duplicate messages is it gives you a better ultimate answer. Why? Because when everyone who wants to help you can see what everyone else has posted, they can make comments/corrections that others may have missed or they may see a modification that can make a good solution that was posted even better. Finally, something else for you to consider... if the volunteers see that you are continually doing multiple posting, they will tend to avoid your messages altogether.

 

[Trebor76]

Well said. Thanks Rick.

 

[tonywatsonhelp]

OK Lots of comments to make here,
Not sure where to start?

First Rick and Trebor, Sorry that you feel this post is a repeat of another, I have tried to explain how this happened and my reasons for doing this in my other thread and will include my comments again at the bottom here so you don't have to go searching for it if you only read this one, I hope it helps to explain my reasoning as I would not want to upset anyone because of this.

The Answer Is, you have helped me so much in the past and you might think your not that an expert but to me your are a genius, some of the solutions you have given me I use on an almost daily basis, So I can not thank you enough. (your code works perfectly, no obvious lag that I could see so thank you again).

Rick, What can I say, Again your help has been amazing and your ability to tweek and imporove on other suggestions is not only welcomed and appreciated by me but I think by those others that post as well. so thank very much your code also worked perfectly as it always does.

Here are the comments I made in my reply to the other post about this being a repeated post, hope it helps you to understand my thoughts on the matter. (there is an irony I'm fully aware of in repeating a reply to a post that was saying I was repeating posts, but I'm going with it! )

My Comments where!

"As for the Thread being duplicated, I'm sorry if you think this is the case but my requests where quite different in my mind,
Maybe to you guys this is "The same as my other thread" but to me they are worlds apart, for example, this thread needs all the formulas up to a certain name converted into values which is exactly what it does, my other thread only wants a small amount of data copied into another area, to me, a very different request as far as im concerned. (And both I needed help on)

Now to make sure I'm not being stupid,, I've compared the VBA code for the 2 threads and I think they are worlds apart! I certainly could not have edited one to create the other, and I do a lot of editing of the codes people help me with so I can use them again on other projects, but this would have been beyond my knowledge. Now to you guys that might sound crazy, but if I can use an analogy!

I'm Sure the Spanish for, "Can you show me where the local toilets are?" uses a lot of the same words and sounds very similar to, "Can you tell me where the local bars and restaurants are located please?" so if your quite fluent in Spanish and know the words for Toilet, bars, restaurant etc. you could probably be helped with the Spanish for the first sentence and work out the second using logic and your own understanding. but if you only know a very small amount of Spanish, and you didn't want to make lots of mistakes, you would probably ask someone to help you with both sentences, even though they may be similar, they are still very different.

The similarity's here are they both require going along a line and looking for a name, then doing something once its found that name, and this is because my brain works in very similar ways when I'm trying to find solutions to problems, so I adopted the same idea of identifying the rows with a header that can be searched for, the "Today" one is so all data before today is no longer a formula, something that was vital for other calculations I was doing in the book and the second is to copy and paste one block of data out of what will be a long string of names and their data and bring it into the second sheet for analysis. and to me these are both very different requests.

I'm not saying you are wrong if you feel the post was repeated, clearly you would not have made this statement if you didn't feel that way so sorry for this, with your skills these two needs are clearly the same, but for me, I felt I needed help with both methods so I asked for both.

I will of course say SORRY, for it was not my intention to upset or annoy any one, I wouldn't know what to do without your help so please don't let this put you off helping me again as you guys are amazing and I really do appreciate all the help everyone has given me."

Once again, thank you so much for your help and I'm very sorry for the misunderstanding.

Tony