Check if value is in range, and if so check cell on row empty

Hiya,

I have a cell with a number in, lets say C1, and another range of an undefined size from A2 downwards. I need to check if the range contains the number in C1. If it does then check if column E on that row is empty. If empty, paste selection, if not empty, message box yes/no, if yes then paste selection.

Thanks in advance for any help.

Tim

Thanks for the reply but it needs to be part of a larger macro. Also, I don't think I made the last bit very clear.

To put it another way.

Check if range in A contains C1. If it doesn't, call another macro. If it does then check if E on that row is empty.
If E is empty then paste selection in that cell. If it isn't empty then present a YesNo message box saying "Would you like to overwrite".
If yes, then paste the selection in the cell. If no then end.

Oops, other people posted the responses while I was writing mine.

Thanks for the replies. I've tried and works perfectly so thanks a lot for the quick response.

Hiya,

I've made a few changes to fit my sheet but I'm now getting an error. Not sure what I've done to it that's wrong.

Please Login or Register  to view this content.

I'm getting "Object variable or With block variable not set" on

If Cells(rEmp.Row, "B") = vbNullString Then

Any ideas?

Tim

Ah no, I see it now. If not sDate should have been if not rEmp.