Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for the existence of a worksheet | Excel Discussion (Misc queries) | |||
Vba - Checking existence of file | Excel Programming | |||
Checking for existence of excel | Excel Programming | |||
Checking for existence of excel | Excel Programming | |||
Checking for the Existence of a Folder | Excel Programming |