searching a range within a range

Hi. Me = macro neophite, mostly just record macros and tweak as need be. But need help with this one.

Trying to enter pay rates into a data sheet when an employee works in a specified department.

My logic may be off, but what I want to do is this:

for each cell in A:A
the cell = employee#
if (the cell in column D is not null AND the cell in column e is blank) then the cell in column D is the department

go to sheet to and for each cell in range A:A of sheet 2
if the active cell = employee and the corresponding cell in column c = dept, then copy the corresponding cell in column D of Sheet 2 and paste it into column E of the first sheet.

I attached a sample of the data. The actual sheets are hundreds of pages long and will take me a couple days to go through without a macro - so if anyone is in a generous mood, I'd appreciate the help.

Thanks.

I know it is really wrong and its embarraising for me to post it, but this where I got so far and decided that I needed help

Please Login or Register  to view this content.

I am not exactly sure what you are doing
This would work in E2 and dragged down
=SUMPRODUCT(--(Sheet2!$A$2:$A$46=A2),--(Sheet2!$C$2:$C$46=D2), --(Sheet2!$D$2:$D$46))
I do not see what determines true or false