Checking for existence of value in another sheet
 

Hi

I have two workbooks. One workbook (transaction file)contains a large
number of entries pertaining to a unique item called "Code." It looks
like:

Code: 100-TTT

Item_Type T1 T2 T3 ... Tn
Item 1 0 1 0
Item 2 1 0 0
..
..
..
Item n 0 1 0

The second workbook (master file)has the same data stored from multiple
files of the type described above. It looks like:

Code Type Item 1 Item 2 ... Item n
100-TTT T1 0 1 0
100-TTT T2 1 0 1
..
..
..
100-TTT Tn 0 0 0
101-WWW T1 0 0 1
..
..
..
102-XXX T1 1 1 1
etc.

I have a VBA program that appends data from the first (transaction)file
to the second (master) file in the correct format.

However I have no check if the data for a particular code has already
been transferred. Thus I need a check if the value "100-TTT" in code
column already exits in the second (master) file, given that it is
found in cell "A1" in the first (transaction) file.

I would appreciate very much if someone could give me as to how I can
achieve this.

Thanks
Utkarsh

Checking for existence of value in another sheet
 

Dim rng as Range, res as Variant
set rng = Workbooks("Master.xls").Worksheets("Data").Range(" A:A")

res = Application.Match(Range("A1"), _
rng, 0)
if iserror(res) then
' code not found
else
' code has already been entered
msgbox "code " & Range("A1") & _
" found at row " & res
end if

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi

I have two workbooks. One workbook (transaction file)contains a large
number of entries pertaining to a unique item called "Code." It looks
like:

Code: 100-TTT

Item_Type T1 T2 T3 ... Tn
Item 1 0 1 0
Item 2 1 0 0
.
.
.
Item n 0 1 0

The second workbook (master file)has the same data stored from multiple
files of the type described above. It looks like:

Code Type Item 1 Item 2 ... Item n
100-TTT T1 0 1 0
100-TTT T2 1 0 1
.
.
.
100-TTT Tn 0 0 0
101-WWW T1 0 0 1
.
.
.
102-XXX T1 1 1 1
etc.

I have a VBA program that appends data from the first (transaction)file
to the second (master) file in the correct format.

However I have no check if the data for a particular code has already
been transferred. Thus I need a check if the value "100-TTT" in code
column already exits in the second (master) file, given that it is
found in cell "A1" in the first (transaction) file.

I would appreciate very much if someone could give me as to how I can
achieve this.

Thanks
Utkarsh

Checking for existence of value in another sheet
 

Thanks Tom! Your code worked like a charm...

Utkarsh


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!