Macro that searches different excel file, searches and copies whatever is in a certain c

[vic41234]

Hey everyone,

I am new to visual basic and I have been trying to figure something out for a while now. Pretty much, I need to create a macro in excel that will search a different excel file for anything that matches a certain criteria, copies whatever the value is in the column c, and paste it back into the original excel file. I have to do this for several items, but if I get the first item down, I think I can figure it out for the rest of them. Anyways, here is an example:

I have item number 5202001. So the macro searches the other excel file for 5202001, but this item must meet certain criteria (this criteria will be the same for every item.) The criteria is that whatever is in cell B must equal CTD or HQ. Whatever is in cell I must be equal to CTDRM.


The macro will find an item that matches this criteria, it copies the value in cell C, which is a quantity, and then pastes it back in the original excel file in cell E38.

Thanks for any help you can give me, I am so lost right now and I have been trying to figure this out on my own for about 2 weeks now.

 

[thuxley]

Hi vic,

For clarity;

You are working in a workbook, and you need to get values that meet the following criteria;

1) Item Number = x (I'm assuming you have more than one item number)
2) Cell B = y (where column in B, is it on the same row as the Item number?, and where y is either CTD or HQ as a value?)

Other;
3) What is CTDRM, and in what cell does this live and in what workbook? The one you need to return the value to, or the one you need to get it from?
4) Again, copies the value to cell C, is this on the same line as the Item Number?
5) Copying into cell E38 in other workbook is fine, however is this process only ever done once, or does it need to keep looking and populate down from E38... E39, E40, E41 etc

 

[vic41234]

thuxley:

1) Yes there are many items in the document, for this macro I have to do it for about 20 of them.

2) Where cell be is HQ or CTD is in B on the same row as the item number. This is the location of the item, I only need to take into account the ones at HQ or CTD.

3) CTDRM is the category of the item and it should be in cell I on the same row as the item number. This is in the workbook I need to get the value from.

4) It needs to copy the quantity in cell c on the same row as the item number

5) This process is going to be done multiple times with the only things changing is the item number and category of the item (eg where what is in cell I is equal to CTDRM). For example, I am going to write a macro after this first one that searches for the same item, except with a different category (lets say CTDWIP) and instead of pasting that value in E38, it will paste the value in F38 on the original workbook. I figure that if someone can help me how to do this for one item, I can figure out how to repeat the process and do it for the rest of the items.

Thanks for all your help!