Macro help required !

[fi.or.jp.de]

Hi All,

I have excel file named as A.xls & B.xls

I get A.xls in morning and B.xls in Evening.( day 1 )

Once i get B.xls I need to compare this workbook with A.xls

ie., Col A has some codes in all workbooks that will repeated some
times.
So, If i know which one is repeated i will prioritize the work.

Here is an example,

A.xls
Col A
ABC45
IND45
AUD45
CZK47

B.xls
Col A
ABC47
IND45
AUD48
AUD46
CZK47
IN45

Compare B.xls with A.xls - there are some repeated codes.
i.e, IND45 & CZK47
i need the word "repeated" if it matches with A.xls in col B for B.xls

What I need ?
I will open B.xls and if i run the macro open dialog will come then I
will select
the A.xls and it need ask which sheet u need match, and then I will
select the sheet.
Then it will match.

File are not constant ...
Tomorrow I will receive C.xls then I need to compare with B.xls
if i received D.xls then I will compare with C.xls...... goes
on ,,,,,,

Thanks a lot in advance.

 

[Barb Reinhardt]

Here's a start. You'll need to provide more information for someone to
assist further, I suspect.

Option Explicit
Sub Test()
Dim aWB As Workbook
Dim bWB As Workbook
Dim aWS As Worksheet
Dim bWS As Worksheet

Set aWB = OpenWorkbook("First")
Set bWB = OpenWorkbook("Second")

'Need code to select the worksheets
' I know this isn't what you're asking for, but it's a start.
' Maybe someone else can assist
' I'd probably set a codename for the worksheet
' and reference that

Set aWS = aWB.Worksheets(1)
Set bWS = bWB.Worksheets(1)

'I'm not sure which columns you want to match
' So you'll need to provide more information
' on that

End Sub

Function OpenWorkbook(myTitle As String) As Workbook
Dim sFile As String
Dim ShortName As String


With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Find and open the" & myTitle & " workbook."
If .Show = False Then
MsgBox ("You cancelled opening the " & myTitle & " file. Execution
ending.")
End
End If
sFile = .SelectedItems
End With

ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))

Set OpenWorkbook = Nothing
On Error Resume Next
Set OpenWorkbook = Workbooks(ShortName)

If OpenWorkbook Is Nothing Then
Set OpenWorkbook = Workbooks.Open(Filename:=sFile)
End If
End Function

'UNTESTED.

 

[fi.or.jp.de]

Hi Barb,

Everyday I receive two excel files only col A has some codes - those
are in sheet2 most of the time. It may come in sheet1 or sheet3.

col A - a.xls Col A - b.xls
123 456
456 abc
789 jhi
abc
efg

I have to run a macro for b.xls here in col A there are 3 codes which
need to match with a.xls Col A.
We can easily make out only 456 & abc are matching. next 456 i need a
word "repeated" as it is
matching with a.xls Col A......

This is what i want. ...

 

[fi.or.jp.de]

Hi Barb,

I have tiny macro to do that job, but that is not complete.
Please assist me to complete this job.

please two workbook named as a.xls & b.xls

Col A put some letter or numbers in both ( a.xls & b.xls - Sheet 2 )
which starts from A2 and end at A10 ( this is an example )

in b.xls put this macro and run it from b.xls .....

Sub matcher()
Dim str As String
Range("B2").Select
a = ActiveWorkbook.Name
Application.Dialogs(xlDialogOpen).Show (str)
b = ActiveWorkbook.Name
Workbooks(a).Activate
Do Until ActiveCell.Offset(0, -1).Value = ""
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=[a.xls]Sheet2!R2C1:R10C1,""repeated"","""")"
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Select
Range("B2:B10").Select
Selection.Copy
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
End Sub

this is running fine only when i know the file name and sheet name ...
this file name and sheet name changes from day to day.
As I inserted file name and sheet name in formula.

Hope u will reply to me..

Thanks