Find Text - Copy Range Below - Paste to Cell

Hi

1/ Need Macro to find text (Any Given) down a column

2/ Copy the immediate range below Text down to 1st blank cell

3/ Paste to given cell (Any Given), in this case S445


Please see Wb for 2 easy Examples to work on

Cheers Dudes


Mike

Hi Mike,

so for one, in example 2 you are pasting in a different range, typo or intended??

for Q2, you only seem to be interested in data from col F being copied and pasted, is this correct?

how do you see the values to search for and which col to paste it into being determined.

Just the first two "what is he actually doing" type questions.

Last question, and no slight intended, what is your VBA macro experience level (helps to know when adding comments into a macro explaining what is being done)

Hi

Q: so for one, in example 2 you are pasting in a different range, typo or intended??

Ans: Not typo , pasted into S445 in both sheets as shown and typed.

Q: for Q2, you only seem to be interested in data from col F being copied and pasted, is
this correct?

Ans: Yes find L350 Column F in this case then copy all cells Below down to 1st variable blank cell / paste into S445 as shown in both sheets

This is Web Extraction Data which will Vary

Q: how do you see the values to search for and which col to paste it into being determined.

Ans:
Macro will search for Text, in this case L350 in Col F
Macro will Copy all Cells Down to 1st Blank Cell (Variable)
Macro will Paste those cells into S445

That is the task at hand

My experience is average copy from Web sites and adjust as needed or hit record and make, but this is a little bit more tricky.

Thanks Cheers

Hi Mike,
slight cross purposes here, perhaps I didn't explain my questions carefully enough

Q1. Yes I agree that they are both pasted into S445 however on tab 2 the actual data is starting around F448 (from memory). MY question was trying to get clear if S445 was a magic position or just Col S in the same row as the text was found? From your two examples I was / am not sure

Q3. again, will the text to be searched for ALWAYS be "L350" or will you want to make it variable. If so will you be entering your search term into a cell in the worksheet, wanting a dialogue box to ask for the value etc.

The task itself is quite easy, just don't want to suggest code that doesn't do what you are expecting

Hi ya

see below

Cheers

OK, I have finished the code, just one last tidy up question left.

IF the output will always commence in s455 THEN the assumption is that there will only ever be one set of data in each input tab that has L350 in col F

My instinct is that you are pulling form and applying the jockey & trainer due for a win type logic and that there will be multiple races results each with the same literal in each download

Mike,

take a look at the code in the attached workbook.

There are actually two versions of the code, the comments tell you the differences

You should be able to adapt one of both sets of code, if you dont need one of the solutions the simply delete from macro code

Find Any Text _Copy Range Below_ Paste_jmac1947.xlsm

Jmac1947

1. Click on the * Add Reputation if you think this post has helped you
2. Mark your thread as SOLVED when question is resolved

Choice

(I lived on your side of the ditch for 4-5 years, used to have a good sample of K1W1 expressions to throw about, not to mention a passable accent )

OK,

so to be very clear, the one macro in this new workbook will only process ONE set of data after it finds the "Start marker" - hard coded to L350 at present.

It will always start the output in S455 irrespective of where it first finds the "Start marker"

Once it finds the first blank cell in Col F after it starts to copy it will end all processing except to save the workbook

Think that about does it Bro, you can mark the thread solved (see thread tools in your first post) and if you think the result has helped you then consider clicking the "* Add Reputation" button on this post.

Hope the horse system works for you, if you want to confide in the system then see my contact details in the macro code

Find Any Text _Copy Range Below_ Paste_jmac1947.xlsm



John Mac.

Dude, you are killing me

OK, that problem is probably because I convert the search term to uppercase so Jockey which i suspect you entered <> JOCKEY which is what the macro is looking to match.

add the following line

Please Login or Register  to view this content.

immediately after the lines

Please Login or Register  to view this content.

and you should be OK.

Just as an side, what data are you expecting in the "magic area" if you select Jockey as your search term?

NOW... if you had been a bit more specific early on then I would have changed the coding to make it considerably easier to change the search term. You were pretty dogmatic about Col F where you didn't actually want only col F

cest la vie

Sweet

Works perfectly finds everything which is good

You are very

Kind
Patient and
Tolerant


To the max

Thanks John

You are a legend a VBA God