Find cells, copy range with criteria and paste with offset

Once again,

too hard for me but I tried a lot


My issue :

I have a commun ID column (i.e column D) on both sheets (i.e sheet1, sheet2) with different data on each rows

for each row x on sheet 2 I'd like to test if : it finds the common ID on sheet 2 + sheet1.range(E:X) is empty

then select range(E:X) from sheet 2

paste it in sheet1.Cells(D).offset or Sheet1.range(E:X)


Important note : I have several ID with different data per sheets and I need to copy each ID from sheet2 to each ID in sheet1. it means when vba is searching for an ID in sheet 2, it shouldn't stop to the first value and to pick up the one without data in the E:X range as it where they are paste.

Do you see what I mean ?

On difficulty I encountered is to "call" the row where vba finds the ID in sheet1


written lke this it doesn't sound really difficult to do but I got confused.

This is one of the code I tried, but either it fails or it gives an error. Sorry I'm learning


thank you

thanks

this is an extract of the "raw file"

Originally Posted by kev_

You want to
- copy columns E:X from sheet "chicks" to sheet "eggs"
When
- all cells in columns E:X in sheet "eggs" are empty AND ID in sheet "chicks" = ID in sheet "eggs"

1) Is the above correct?

Almost,

1/ in fact exactly, it would be from E:BE from "chicks" to "eggs" (it was to make it easier with E:X for the example and I feel confident enough to edit your code )


2/ In "eggs", te "paste range" is in Z :BZ (and not E:X) where all cells are empty and and the top row is in yellow.

3/ ID "eggs" = Id "chicks" = column D

2) There are different numbers of rows in the 2 sheets for each col/nest combination. WHY different no of rows?
eg col8/nest16 has 2 rows in "chicks" and 3 rows in "eggs"

They are different because not all eggs hatched. If an egg hatches it will have a row in "chicks"

3) There are multiple rows with the same ID which contain different values in some cells (applies to both sheets) - so how do we tell VBA what to copy and where to paste? What is the rule? (in words)

there is no proper rule. Which means, a chick doesn't have to be link with a precise egg !

A chick could be pasted to any egg of the same ID... I will adapt every thing manually afterwards.

Perhaps you could attach workbook with 3 sheets (create the 3rd sheet manually to show what you want to see)
- sheet "chicks"
- sheet "eggs" with range of empty cells in columns E:X
- sheet "eggs AFTER paste"

please consider the following workbook.
I did it until col 8 nest 13_2

thank you

thanks for your help

Column AE in sheet "eggs" is the Hatching Date

in this project we consider only one hatching date per nest, so don't trouble yourself too much with it

FOR each ID in sheet "eggs"
IF all cells in columns Z:BZ are empty (all rows for that ID)


THEN for each ID in sheet "chicks"
- find next matching ID
- copy that row for the ID (columns E:BE only)
- paste to sheet "eggs" (columns Z:BZ)

like this it sounds perfect I guess

EDIT
How can Z:BZ be empty ? AE should have values for any eggs that hatch
So which columns in sheet "eggs" are we checking are empty?

As I said, only one value per nest for the hatching. only the first hatched egg matters.


does that help ?



thank you very much once again

i've been busy this evening sorry
I took a leave for 2 weeks and I won"t be able to check my emails on regularly basis but tomorrow I'll work on it, I want it to be done.

Hi kev_


I apologise to come back on this thread 2 weeks after, I would have prefer it to be done before I left ..


We might had a misunderstanding because what you've done differs slightly from what I expected. I must have been confused at some point, hence let me explain it again :


each row in chicks should find a match in egg's sheet but as there is more eggs than chicks, some rows in eggs have to remain empty.
In the previous code you did, it is always the first chick's ID which is pasted as many times as there is rows in eggs and as long as it matches, which is not what I expected.


My idea was :

for each rows in chicks
find a match where e.range(Z:BZ) is empty and paste c.range(F:BF) in e.Z

My idea with the empty cells was to avoid overwriting chick's data on a egg's range which already found a match (but maybe it is useless ?)


By this way, I thought every chicks will found a match and some rows in Egg will remains empty as there is less chick's rows than egg's.


I'll try to edit you code while you could find a solution


many thanks again for your help.

I hope I make it clear.

I edited your code as following

I think it could work but I might have an issue with the "on error resume next" because nothing is happening ..

Please Login or Register  to view this content.

what do you think ?

Hi Kev_,

sorry for asking but did you find something ? I'm struggling with this code

Thank you

Hi,

I hope it will help


thanks

Finally, I decided to start this process manually.

No worries
thank you for your help


fk