Macro Help...
 

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


--
polabonez
------------------------------------------------------------------------
polabonez's Profile: http://www.excelforum.com/member.php...o&userid=30748
View this thread: http://www.excelforum.com/showthread...hreadid=504138

Macro Help...
 

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

Thanks


--
polabonez
------------------------------------------------------------------------
polabonez's Profile: http://www.excelforum.com/member.php...o&userid=30748
View this thread: http://www.excelforum.com/showthread...hreadid=504138

Macro Help...
 

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)
--
Kevin Backmann


"polabonez" wrote:


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


--
polabonez
------------------------------------------------------------------------
polabonez's Profile: http://www.excelforum.com/member.php...o&userid=30748
View this thread: http://www.excelforum.com/showthread...hreadid=504138

Macro Help...
 

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!

"polabonez" wrote:


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

Thanks


--
polabonez
------------------------------------------------------------------------
polabonez's Profile: http://www.excelforum.com/member.php...o&userid=30748
View this thread: http://www.excelforum.com/showthread...hreadid=504138

Macro Help...
 

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.

--
Regards,
Tom Ogilvy

"Kevin B" wrote in message
...
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)
--
Kevin Backmann


"polabonez" wrote:


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


--
polabonez
------------------------------------------------------------------------
polabonez's Profile:
http://www.excelforum.com/member.php...o&userid=30748
View this thread:
http://www.excelforum.com/showthread...hreadid=504138

Macro Help...
 

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...
--
Kevin Backmann


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Kevin B" wrote in message
...
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)
--
Kevin Backmann


"polabonez" wrote:


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


--
polabonez
------------------------------------------------------------------------
polabonez's Profile:
http://www.excelforum.com/member.php...o&userid=30748
View this thread:
http://www.excelforum.com/showthread...hreadid=504138