Macro Help...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

polabonez - 23 Jan 2006 19:03 GMT

Can someone help me with a macro / code to perform this task?

I have two excel files, or sheets... One is a list of different part
numbers in column A and their list prices in column B. This is a LIST
PRICE file for a brand of parts my company sells.

On the other sheet, I have a list of part numbers for this brand of
part... maybe 60-70 different numbers. Is there a macro I can run, so
that I do not have to look up all of the list prices individually and
enter them manually? I tried to create a simple macro by recording my
keystrokes but couldn't make it work.

For instance, I would highlight the number I want to get the price on,
switch to the sheet that has the list prices, control F to find the
part no., move one square the the right, copy the price, and paste it
back into my original sheet... However this did not work because the
program always used the number I was pasting to search for, hence using
number 12346 instead of just Pasting it....

I know this may be confusing, but if anyone understands or I can help
you understand the problem better, let me know.

Thanks

Signature

polabonez

Reply to this Message

polabonez - 23 Jan 2006 19:19 GMT

Or if it isn't a macro, any other solution you could point me to.

Thanks

Signature

polabonez

Reply to this Message

Kou Vang - 23 Jan 2006 19:45 GMT

Yes there is definitely VBA that could help you out here, but as you will
find out there are about 10 different ways to solve every problem. If you
want something that is clean and neat, then I would recommend VBA or a Macro.
If you want something quick and dirty, it sounds like all you need is a
simple formula. I will give you the simple formula and hopefully that will
be enough.

Sheet 2 has Column A with Part numbers. You want Sheet 2 Column B to have
the list prices. So in Column B Cell 2 (or where ever you want to start
listing the price) use the Vlookup formula:

=Vlookup(ColumnACell,Beginning of Sheet 1 Range: End of Sheet 1 Range,
Column of Sheet 1 value you want displayed, True or false Return)

your formula should look similiar to this:

=Vlookup(A2,'Sheet1'!a$2:'Sheet1'!b$150,2,false)

Try using the help to find how to use the Vlookup function. Hope this
helps, good luck!

> Or if it isn't a macro, any other solution you could point me to.
>
> Thanks

Reply to this Message

Kevin B - 23 Jan 2006 19:40 GMT

If you sort the pricing table you could use a Vlookup function.

VLOOKUP(Value_To_Lookup, Lookup_TableRange, Column#_of_Return_Value)

For instance, if the the part number your looking up in an cell A1 of Sheet
2, and the pricing table is on Sheet1, cells A1 through B6, and the price of
the part number is in column 2 of the pricing table, the formula below would
return the price.,

=VLOOKUP(A1,Sheet1!$A$1:$B$6,2)

Signature

Kevin Backmann

> Can someone help me with a macro / code to perform this task?
>
[quoted text clipped - 19 lines]
>
> Thanks

Reply to this Message

Tom Ogilvy - 23 Jan 2006 19:55 GMT

Vlookup doesn't require that the lookup table be sorted if you are looking
for an exact match and you set the fourth argument to false.

Signature

Regards,
Tom Ogilvy

> If you sort the pricing table you could use a Vlookup function.
>
[quoted text clipped - 30 lines]
> >
> > Thanks

Reply to this Message

Kevin B - 23 Jan 2006 20:40 GMT

Hey, thanks for the heads up, I completely forgot about that. Some formulas
in my head will forever be locked in Lotus 1-2-3 (DOS version) mode, and
vlookup is one of them, pmt is another.

Thanks again...

Signature

Kevin Backmann

> Vlookup doesn't require that the lookup table be sorted if you are looking
> for an exact match and you set the fourth argument to false.
[quoted text clipped - 40 lines]
> > > View this thread:
> http://www.excelforum.com/showthread.php?threadid=504138

Reply to this Message