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

vic41234

New Member
Joined
May 27, 2010
Messages
2
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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

thuxley

Board Regular
Joined
Mar 9, 2005
Messages
101
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(n) = 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(n), 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

New Member
Joined
May 27, 2010
Messages
2
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,456
Messages
5,601,772
Members
414,472
Latest member
Chris_1990

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top