Update Sheet1
 

Hello everyone,

I have already made Microsoft Access databases but now I need to make one
small VB program in Excel.
I have 2 Sheets.
In Sheet 1 I have 3 columns like this:

Reference number Description Price/Eur

689023 Socket 2,45
23489 Plug 0,98

There is a lot of reference numbers (cca 8 000).

In Sheet 2 I have table just like this but with different Prices and small
number of
referent numbers (cca 200).

How can I make VB code which will find all Referent numbers from Sheet 2 in
Sheet 1 and put new Price from
Sheet 2 to Sheet 1?

thank you in advance,

Update Sheet1
 

I wouldn't bother using VB - just use a formula to display the result
in a new column next to the original price column, i.e:

Reference Number Description Price/Eur New Price/Eur

In the new column put a formula to lookup the value from sheet2. I'd
suggest the easiest formula would be a VLOOKUP(), e.g. in cell D2 put:

=VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)

(see Excel Help for more about the function)

If the formula doesn't find a match then it will return #N/A, but you
could use the ISNA() function to capture that and put the original
price in its place, or something else, e.g.

=IF(ISNA(VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)),C2,VLOO KUP(A2,Sheet3!A1:C3,3,FALSE))




Steve wrote:
Hello everyone,

I have already made Microsoft Access databases but now I need to make one
small VB program in Excel.
I have 2 Sheets.
In Sheet 1 I have 3 columns like this:

Reference number Description Price/Eur

689023 Socket 2,45
23489 Plug 0,98

There is a lot of reference numbers (cca 8 000).

In Sheet 2 I have table just like this but with different Prices and small
number of
referent numbers (cca 200).

How can I make VB code which will find all Referent numbers from Sheet 2 in
Sheet 1 and put new Price from
Sheet 2 to Sheet 1?

thank you in advance,

Update Sheet1
 

Thank you Rob,

problem is that I do not have space for another column in Sheet 1, can I
with this function replace old Price
with new one from Sheet 2 and is there a way to change colour of cell which
has changed Price?

thank you in advance,


"Rob Hick" wrote in message
oups.com...
I wouldn't bother using VB - just use a formula to display the result
in a new column next to the original price column, i.e:

Reference Number Description Price/Eur New Price/Eur

In the new column put a formula to lookup the value from sheet2. I'd
suggest the easiest formula would be a VLOOKUP(), e.g. in cell D2 put:

=VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)

(see Excel Help for more about the function)

If the formula doesn't find a match then it will return #N/A, but you
could use the ISNA() function to capture that and put the original
price in its place, or something else, e.g.

=IF(ISNA(VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)),C2,VLOO KUP(A2,Sheet3!A1:C3,3,FALSE))




Steve wrote:
Hello everyone,

I have already made Microsoft Access databases but now I need to make
one
small VB program in Excel.
I have 2 Sheets.
In Sheet 1 I have 3 columns like this:

Reference number Description Price/Eur

689023 Socket 2,45
23489 Plug 0,98

There is a lot of reference numbers (cca 8 000).

In Sheet 2 I have table just like this but with different Prices and
small
number of
referent numbers (cca 200).

How can I make VB code which will find all Referent numbers from Sheet 2
in
Sheet 1 and put new Price from
Sheet 2 to Sheet 1?

thank you in advance,

Update Sheet1
 

No, you need to put the function in a new cell because it needs to
reference both the old price and the new price. It seems strange that
you have no room on Sheet1 - you could always insert a new column if
the current one has some data in. Failing that you will have to insert
a new sheet and put references to the original cells on Sheet1.

You can colour code those cells that have changed by using 'conditional
formatting' from the Format menu. It's a bit difficult to explain
exactly what to do in this post but if you have a look in Excel Help,
there is a fairly comprehensive guide.

Rob


Steve wrote:
Thank you Rob,

problem is that I do not have space for another column in Sheet 1, can I
with this function replace old Price
with new one from Sheet 2 and is there a way to change colour of cell which
has changed Price?

thank you in advance,


"Rob Hick" wrote in message
oups.com...
I wouldn't bother using VB - just use a formula to display the result
in a new column next to the original price column, i.e:

Reference Number Description Price/Eur New Price/Eur

In the new column put a formula to lookup the value from sheet2. I'd
suggest the easiest formula would be a VLOOKUP(), e.g. in cell D2 put:

=VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)

(see Excel Help for more about the function)

If the formula doesn't find a match then it will return #N/A, but you
could use the ISNA() function to capture that and put the original
price in its place, or something else, e.g.

=IF(ISNA(VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)),C2,VLOO KUP(A2,Sheet3!A1:C3,3,FALSE))




Steve wrote:
Hello everyone,

I have already made Microsoft Access databases but now I need to make
one
small VB program in Excel.
I have 2 Sheets.
In Sheet 1 I have 3 columns like this:

Reference number Description Price/Eur

689023 Socket 2,45
23489 Plug 0,98

There is a lot of reference numbers (cca 8 000).

In Sheet 2 I have table just like this but with different Prices and
small
number of
referent numbers (cca 200).

How can I make VB code which will find all Referent numbers from Sheet 2
in
Sheet 1 and put new Price from
Sheet 2 to Sheet 1?

thank you in advance,

Update Sheet1
 

Hello Rob,

thank you for your advice, it works.It works in a way that all references in
Sheet 2 must be in sort order just
like references in Sheet 1.I have tried to put one reference number and
changed it price in Sheet 2 but on different place
then in Sheet 1 and it does not work.Can I make additional code which will
search only references in Sheet 1 which I have chosen in Sheet 2
and in sort order I want?

thank you in advance,






"Rob Hick" wrote in message
ps.com...
No, you need to put the function in a new cell because it needs to
reference both the old price and the new price. It seems strange that
you have no room on Sheet1 - you could always insert a new column if
the current one has some data in. Failing that you will have to insert
a new sheet and put references to the original cells on Sheet1.

You can colour code those cells that have changed by using 'conditional
formatting' from the Format menu. It's a bit difficult to explain
exactly what to do in this post but if you have a look in Excel Help,
there is a fairly comprehensive guide.

Rob


Steve wrote:
Thank you Rob,

problem is that I do not have space for another column in Sheet 1, can I
with this function replace old Price
with new one from Sheet 2 and is there a way to change colour of cell
which
has changed Price?

thank you in advance,


"Rob Hick" wrote in message
oups.com...
I wouldn't bother using VB - just use a formula to display the result
in a new column next to the original price column, i.e:

Reference Number Description Price/Eur New Price/Eur

In the new column put a formula to lookup the value from sheet2. I'd
suggest the easiest formula would be a VLOOKUP(), e.g. in cell D2 put:

=VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)

(see Excel Help for more about the function)

If the formula doesn't find a match then it will return #N/A, but you
could use the ISNA() function to capture that and put the original
price in its place, or something else, e.g.

=IF(ISNA(VLOOKUP(A2,Sheet3!A1:C3,3,FALSE)),C2,VLOO KUP(A2,Sheet3!A1:C3,3,FALSE))




Steve wrote:
Hello everyone,

I have already made Microsoft Access databases but now I need to make
one
small VB program in Excel.
I have 2 Sheets.
In Sheet 1 I have 3 columns like this:

Reference number Description Price/Eur

689023 Socket 2,45
23489 Plug 0,98

There is a lot of reference numbers (cca 8 000).

In Sheet 2 I have table just like this but with different Prices and
small
number of
referent numbers (cca 200).

How can I make VB code which will find all Referent numbers from Sheet
2
in
Sheet 1 and put new Price from
Sheet 2 to Sheet 1?

thank you in advance,