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
|