Cell contains contents of another cell

**atticuslodius** · 07-19-2007, 02:22 PM

Hello,
My problem is simple, but I cant figure it out. I have 2 sheets in my workbook their names are 1 and 2. when something is entered into cell D10 of sheet 1, I want that to check sheet 2 column D if the same thing is in any of those cells. The whole cell doesnt have to contain it, just a portion of it...

for example.. Sheet 1, Cell D10 has "PCD-23" written in it .. if Sheet 2, Column D has "S-01, PCD-23, J-01" I want it to prompt me with a message box saying my preference...

If someone could start me off I believe I could get it from there.

**ilovedurango** · 07-19-2007, 04:45 PM

I think I understand your question...when you enter a value in D10 in sheet1 that is one of the values in the other sheet, you want it to prompt you about which value to select?

Why don't you just make Sheet2's cells = to sheet's one cells? That would not require a macro. If you want a form to pop up, that's much more complicated coding.....

**boylejob** · 07-19-2007, 05:50 PM

atticuslodius,

See if this will get you started. I put it on the Worksheet_Change event and set it up to fire when cell D10 on Sheet1 changes. It looks for the value in Column D of Sheet2. Gives you a Message Box and tells you whether the item was found or not found.

Please Login or Register  to view this content.

Hope this helps!

**atticuslodius** · 07-20-2007, 09:06 AM

I tried the code boylejoe... however, I cant get it to do a thing for me.. I only get errors.

ilovedurango-What I want the code to do is... I have a 5 fields that you enter on sheet 1 and when you press a button they are transfered to sheet 2.

I want it to be when I hit the button that the macro checks cell D10 Sheet1 and make sure it is not in Column D Sheet2. (It does not have to be an exact match, for example (((if d10sheet1 entered value is "PCD" then when you press the button to transfer those fields to check columnDSheet2 to make sure it is not in one of those fields.. and columnDsheet2 may be something like SOP, PCD, FI ... etc.. so there may be more than just PCD in the an individual cell in ColumnDsheet2. I want it to be able to match part of the cell to find it.

**boylejob** · 07-20-2007, 09:22 AM

atticuslodius,

1. What are the error messages you are getting? Keep in mind that I have no idea, other than the information you have provided, as to what you data actually looks like. Posting a copy or sample of your workbook is always very helpful.

2. Where did you put the code? It has to go under Microsoft Excel Objects under Sheet1 because it is being triggered by a change is a cell on that sheet.

3. You mentioned in you last post a button. Do you want the code to fire automatically when you enter a value in D10 or do you want to enter a value in D10 and press a button before it searches.

If you can answer these questions, I'll see if I can figure out what is wrong.

**atticuslodius** · 07-20-2007, 10:13 AM

Let me explain to you the functionality…
((Sorry I had to remove the real information because it was proprietary; Sheet 2 is example data of how it would actually look.))

-Sheets 1 and 2 and VBA are all password protected (password = ecolog)

How it Works
Sheet 1-Click Generate ECO Number to get the next available ECO number from Sheet 2
-Issued To field is a drop down menu
-Document # = This is Cell D10, it is a free text field.
-To Rev will be the document # Rev… Disregard this field
-Document Type is a drop down menu
-Other … Disregard this field

Required fields are D5, D8, D10, and D13 or you will be prompted not all information is complete.

Click “Save” (The grey button not the one in the tools menu)

All fields will be transferred to Sheet 2 and Sheet 1 will be cleaned.

You will get a message box that says “Do you want to issue a new ECO Number?” (If you click NO on this field it will automatically close out the workbook.

Sheet 2-This page is also protected (password = ecolog)
-Click Edit in the top right to open Unprotect Sheet function and enter pass
-Click Save in the top right to automatically lock the worksheet again and save changes.

NOTE: It is programmed several times in different codes to relock or unlock fields so you will need to watch for this.

MY ISSUE:
I want Sheet1 Cell D10, when you press the save button to check Sheet2 Column D for a match of that document number even if it is listed as in the example, “AAA, BBB, CCC” and I enter BBB in Cell D10. I still want it to prompt me that an ECO is already open if Sheet 2 Column H for that cell does not have a date in it.

The macro that I have for this is listed under “Sub EnterData()” [Sub Enter Data is the macro assigned to the grey Save button].

I tried to distinguish easily where my current macro is I have listed it as CHECKTEST<<<< under “Sub Enter Data()”

The Current macro that it calls is listed under Module 4 for Sub Macro99() … the Only problem with this is… Sheet2 Column D has to be an exact match of cell D10…. So if D10 = EEE and there is a cell in Sheet2 Column D that is EEE and Column H is empty than it will prompt you with a message that an ECO is already open.

I want it to be able to give me the same message if D10 = EEE and there is a cell in Sheet 2 Column D is AAA, BBB, EEE and Column H is empty.

Hope that explains it… See attached for database.
On a side note… Do you think this is a good setup? Do you think I did a decent job with the functionality of the Excel File?

**boylejob** · 07-20-2007, 04:26 PM

atticuslodius,

Here is how I would probably end up doing it. If 5 other folks were to look at it, you would probably get 5 different answers, but this is how I would do it.

In this section of code

Please Login or Register  to view this content.

I would create some kind of a loop since you are not just looking for a Document #, but you are also looking at Date Closed. In the loop I would code something like this

Please Login or Register  to view this content.

You will have to adapt this to your code, but you appear to have a good grasp on coding so I don't think that will pose a problem for you. I may not have the If statement set up the way you want so look at it carefully. Again, this is probably not the only way of addressing your problem and may not even be the most efficient method, but it is how I would do it based on what I know (I'm not an expert and I don't program for a living).

On a side note… Do you think this is a good setup? Do you think I did a decent job with the functionality of the Excel File?

I doubt that you will find two programers that set things up the same way. I have been programming in various languages since about 1988. I have been working with VBA for about 7 years. I program to my the processes within my office run more efficiently. I am constantly changing the way I code things, especially since I found this forum. Unless you have been programming forever and have ALL the rules perfected, your program can probably be improved upon. Does that mean it is bad code, certainly not! My old code works beautifully, but not necessarily efficiently. I am always seeking to improve it.

Now for my side note that is not related - where in this small world are you. Since this forum is global, I always find it interesting to see where you are on the globe.

I hope all of this helps or at least points you in the right direction.