Comparision of values between files

Closed
saru - Jun 19, 2010 at 05:51 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 20, 2010 at 12:07 PM
Hello,

I am trying to find the vb coding for the below requirement

Few numbers in one xls file
Huge numbers in another xls file
Match numbers from file 1 to file 2 and if file 1 number not present in file2 and less than 5 or 6 digits it should show an error in file 1 red colour.

Venkat can you help me on this please?



Related:

3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 19, 2010 at 07:21 AM
1. please do not mention the meber who needs to answer. anybody who can give the answer will give it
2. you must give complete information. in each workbook in which column the data is entred.

I made two sample workbooks. "book3" and "book4"
the data in the first workbook in sheet1 in column A from A1 down are entries

1
2
3
4
5

in the second workbooks called "book4" in sheet1 column A the data from A1 entered are as follows

62
80
90
69
98
21
33
93
96
1
60
46
39
88
54
80
68
36
9
66

now in such pair of sample workbooks

the following macro "test" will give the result (the second macro undo will undo the result of the macro. you an park these two macros in the mdoules in the vbeditor of either of the workbooks.

KEEP BOTH THE WORKBOOKS OPEN. i PRESUME BOTH THE BOOKS ARE SAVED WORKBOOKS.

The result will be in sheet1 of ook3 entries 2,3,4,5 are colored red because they are not avaiable in book4 and also less than 5 digits.

the macros are :
========================
Sub test()
Dim r As Range, c As Range
Dim x, cfind As Range
With Workbooks("book3.xls").Worksheets("sheet1").Columns("A:A")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
For Each c In r
x = c.Value
With Workbooks("book4.xls").Worksheets("sheet1").Columns("A:A")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then GoTo nextc
End With

If Len(c) < 5 Then c.Interior.ColorIndex = 3
nextc:
Next c
End With

End Sub
==========================
Sub undo()
With Workbooks("book3.xls").Worksheets("sheet1")
.Cells.Interior.ColorIndex = xlNone
End With
End Sub
====================
modify sutiably
your comments please
0
Hi Venkat,

I am getting an error when i am doing the above mentioned code.

My requirement is if in an excel file(abc) i have a base data of huge numbers
and in another excel file (def) in a particular column (ex: Column c) i enter a number so the macro should check the number entered by me in the base file (1st file) and through an error if the number does not match the base file.

The error message should pop up saying number incorrect / number not present in database.

Could you help me on this.
0
When i enter this code in the 1st file which has the huge data (my base file) i get an error saying "" Compile error: Expected:named parameter""

Also to perform this activity each time do i have to keep both workbooks open?
Is there a way that i can keep the base file and the file i want to match in one folder, so that any user enteres a number if it matches the databse "OK" else it should pop up an error.

Could you help on this please
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 20, 2010 at 06:30 AM
suppose the file abc-sheset1 has larged number of numbers in MANY columns
sample sheet
29 2 71 41 22
41 49 10 90 16
34 35 28 98 6
35 81 23 11 44
62 7 86 12 2

KEEP THIS FILE (WHICH IS A SAVED FILE) OPEN

inthe file def right click the tab of sheet1 and click view code
in the resuting window copy this EVENT CODE

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cfind As Range, r As Range, x
If Target.Column <> 3 Then Exit Sub
x = Target.Value
Set r = Workbooks("abc.xls").Worksheets("sheet1").UsedRange
Set cfind = r.Cells.Find(what:=x, lookat:=xlWhole)
If Not cfind Is Nothing Then
Exit Sub
Else
MsgBox "number not in the database"
End If

End Sub


now go to sheet1 of file def in any cell in column C type any number and see
1. if that number is availabe in sheet1 of the file abc NOTHING will happen
2. in case that number you have entered is not available in the file abc sheet1 then error message will come up

In the EVENT CODE there is a statement
If Target.Column <> 3 Then Exit Sub
the number 3 here refers to column C. If the columnin which you want to enter numbers in shseet of file def is different modify this statement.
The purpose of this statement is if you enter some number in SOME OTHER COLUMN nothing will happen whther that number is or is not available in sheet1 of abc.
0
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jun 20, 2010 at 12:07 PM
the event code should be parked in the sheet code where there is NO data.. the name of the file is not relevant

The other file where there is huge data should be called "abc.xls". if this name is different change in the following statement


Set r = Workbooks("abc.xls").Worksheets("sheet1").UsedRange

at present work with both the files open

It looks to get information or referring to a closed file you may have to use a function. but this method has some limitations. So it is easier to get the information from the open files. The only thing you can do is open the file and after the event code statements are invoked you can close the file automatically by statements in the same event code. I wonder why this tortuous way when it is easier to keep the file open. If you have specific difficulty in keeping the file open I can give you a few more statements to open the file and close the file . If necessary revert back to the newsgroup.
0