vba code in excel please help

indep

Hi, could you please help me with the following, I am trying to check if a value exists in a table and then if it is to copy the row where the value is stored in another sheet so I have values In table A1:A10 in worksheet 1 with values associated B1:D10, then someone enters a value in worksheet 2, I want a macro to check if the value entered matches a value in A1:A10 if no then a 0 appears, if yes then it gives me the value in the B column associated with it

EX: A1 in worksheet 1 is 1 N N N if someone enters 1 it returns N N N in each row

zibadian

: Hi, could you please help me with the following, I am trying to
: check if a value exists in a table and then if it is to copy the row
: where the value is stored in another sheet so I have values In table
: A1:A10 in worksheet 1 with values associated B1:D10, then someone
: enters a value in worksheet 2, I want a macro to check if the value
: entered matches a value in A1:A10 if no then a 0 appears, if yes
: then it gives me the value in the B column associated with it
:
: EX: A1 in worksheet 1 is 1 N N N if someone enters 1 it returns N N
: N in each row
:
:
You don't need to use a macro for that. You can use the vlookup() function for that. Example:
[code]
=vlookup(B1;Sheet1!A1:D10;2;FALSE)
[/code]
This searches Sheet1!A1:D10 for matches and returns the value in the 2nd column of the row found, or #N/A if nothing is found.
If you want to remove the #N/A and replace it with a default value, then place the vlookup() into a temporary cell, and add a code like this into the final cell:
[code]
=if(iserror(ZZ1);"0";ZZ1)
[/code]
This example assumes that the previous example is placed in cell ZZ1.

indep

Thanks that helped a lot!!


: : Hi, could you please help me with the following, I am trying to
: : check if a value exists in a table and then if it is to copy the row
: : where the value is stored in another sheet so I have values In table
: : A1:A10 in worksheet 1 with values associated B1:D10, then someone
: : enters a value in worksheet 2, I want a macro to check if the value
: : entered matches a value in A1:A10 if no then a 0 appears, if yes
: : then it gives me the value in the B column associated with it
: :
: : EX: A1 in worksheet 1 is 1 N N N if someone enters 1 it returns N N
: : N in each row
: :
: :
: You don't need to use a macro for that. You can use the vlookup()
: function for that. Example:
: [code]:
: =vlookup(B1;Sheet1!A1:D10;2;FALSE)
: [/code]:
: This searches Sheet1!A1:D10 for matches and returns the value in the
: 2nd column of the row found, or #N/A if nothing is found.
: If you want to remove the #N/A and replace it with a default value,
: then place the vlookup() into a temporary cell, and add a code like
: this into the final cell:
: [code]:
: =if(iserror(ZZ1);"0";ZZ1)
: [/code]:
: This example assumes that the previous example is placed in cell ZZ1.