copying a range

Hello


I'm trying to write code that will find a cell based on fixed values (can be coded in the macro) and then copy the values of that cell and the 3 cells below to another column say C. In the end it will need to loop and consequently extract multiple sets of cells to copy to the new columns


I can find and copy the first cell but i'm not able to copy the 4 cells together using offset.
Main question then is how do I find and copy a range of 4 cells?

Unfortunately i don't have the code here with me, so i can paste what i got/tried sofar. Will do that tomorrow or later this day. But hopefully someone can hint me in right direction.


Thanks Very Much

Science Boy

Hello Science Boy,

Here is how to 4 rows of cells based on the first cell found to a new destination. This macro takes 2 arguments: the source cell (the one Find returned), and the new cell location. This allows the macro to be used later in a loop.

Please Login or Register  to view this content.

Using the Macro

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks Leith,

Now lets see if I can make this work. If not i'll be back

Leith, or someone else

-if this should be in a seperate topic let me know and i'll start one-

I've used the code and used it in the macro below. Its a query to retrieve traffic information and then copy selected roads to another part of the sheet. However I need a way to make this loop as there will be more than 1 notification of the same road when it is very busy and i like to see all of them. I will probably be looking at a max of five individual roads, like A1. A12, A14. (not like A1, A2, A3). I've coded for 2 now but i suppose 2 or 5 shouldn't be much of a difference with regard to method of coding.

I also had to use the on error resume next because if it doesn't find anything the macro fails.

Question: How to loop this properly?
Question: Is this a "proper" way and/or can i take shortcuts somewhere?

Please Login or Register  to view this content.

Hello Science Boy,

Create a second macro. You can this call this to search for and copy the info for the road you specify. Here is the code. An example follows the code.

Macro Code

Please Login or Register  to view this content.

Example

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Leith,


I put the macros all in module 1, but it fails with an error 424 object required and this is highlighted

Please Login or Register  to view this content.

Moving mouse over it, it says RD=nothing and road= "File: A10"

btw why is it Cell(1,1) in the .find parameters?


Any ideas on why it doesn't work?

Cheers

Science Boy

Hello Science Boy,

I forgot to remove the Activate from the Find when I did the editing. The code should be as follows...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Leith,

There was another typo in the code which drove me crazy till i found it,

Please Login or Register  to view this content.

activeCell should be Rd and it works like a charm.

Thanks for helping me

Hello Science Boy,

Glad it is working. I apologize again for my typing errors.

Thanks,
Leith Ross