Instead of posting a new thread in this forum, I thought it would be
better if I give a link to the original post.
Here is the link: http://www.mrexcel.com/board2/viewtopic.php?t=294981&highlight=
1) Are the master and Employee files located in the same folder? (it
would make it easier)
2) Are the employee names always in Column A?
3) Are the dates always in Row 2 of master? (does data always start in
A3?)
4) Are the employee name files identical to the names in the master
(except with .xls at the end)
5) How do you know to extract the flight #'s (I'm assuming) from cell B3?
Does the data always start after the 2nd space? Where does it end?
6) Having merged cells makes it a bit more complicated, but it can be done.7_
7) How many employees do you need to check?
You may have already answered some of this. If I have time later, I'll
post info on how I'd start on this. This will have to be a step wise
process.
--
HTH,
Barb Reinhardt
--
HTH,
Barb Reinhardt
I don't need an answer quickly, I have been doing this manually for
two months. I have also tried lot of things for my brother but the
kind of macros I know, it is kind of difficult for me.
>
> 1) Are the master and Employee files located in the same folder? (it
> would make it easier)
Yes. C:\Data
> 2) Are the employee names always in Column A?
Yes.
> 3) Are the dates always in Row 2 of master? (does data always start in
> A3?)
Yes. (yes)
> 4) Are the employee name files identical to the names in the master
> (except with .xls at the end)
Yes.
> 5) How do you know to extract the flight #'s (I'm assuming) from cell B3?
> Does the data always start after the 2nd space? Where does it end?
I did not understand this question.
> 6) Having merged cells makes it a bit more complicated, but it can be done.7_
I cannot help on this. The excel file is extracted from a software
which pulls the result in merged cells.
> 7) How many employees do you need to check?
This is just a sample data, In real, 250+ employees.
>
> You may have already answered some of this. If I have time later, I'll
> post info on how I'd start on this. This will have to be a step wise
> process.
> --
> HTH,
> Barb Reinhardt
I will also put a message on mrexcel that I have posted this question
here. Thank you so much
Now Insert a Module
In that module paste the following:
Sub FindDuplicates()
Dim aWB As Workbook
Dim aWS As Worksheet
Dim WS As Worksheet
Dim oWB As Workbook
Dim oWS As Worksheet
Dim lRow As Long
Dim lCol As Long
Dim myCol As Long
Dim myRow As Long
Dim myEmployee As Range
Set aWB = ActiveWorkbook
For Each WS In aWB.Worksheets
If WS.CodeName = "Master" Then
Set aWS = WS
Exit For
End If
Next WS
Set WS = Nothing
If aWS Is Nothing Then
MsgBox ("The worksheet with code name Master does not exist in the " &
vbNewLine & _
"active workbook")
End If
'Determine last row of data in master workbook (in column 1)
lRow = aWS.Cells(aWS.Rows.Count, 1).End(xlUp).Row
'Determine last column of data in master workbook Row 2
lCol = aWS.Cells(2, aWS.Columns.Count).End(xlToLeft).Column
For myRow = 3 To lRow
Set myEmployee = aWS.Cells(myRow, 1)
If Not IsEmpty(myEmployee) Then
If LCase(myEmployee.Value) <> "jr" And _
LCase(myEmployee.Value) <> "sr" Then
For myCol = 2 To lCol
Debug.Print myEmployee.Value, aWS.Cells(2, myCol).Value,
aWS.Cells(myRow, myCol).Value
Next myCol
End If
End If
Next myRow
End Sub
That's a start to getting what you want.
--
HTH,
Barb Reinhardt
I think I would need another piece of code in the [ For myCol = 2 To
lCol AND Next myCol ] loop
Now the logic what i am thinking of is just after the For myCol = 2 To
lCol line, I should open the EMPLOYEE A.XLS file and check the numbers
corresponding to date in the aWS.Cells(2, myCol).Value. After that
find those numbers in the aWS.Cells(myRow, myCol).Value string and
continue this process till the end of all dates. Once done, close the
file and open EMPLOYEE B.XLS file.
I believe my thought process is correct. I am going to try this out
tomorrow evening. Not sure if I can do it but I will definitely give
it a try.
Just a question: the two variables Dim oWB As Workbook and Dim oWS As
Worksheet. Have you declared it to open the other files in the c:\data
folder?
Thanks a ton
On Oct 3, 7:25 pm, Barb Reinhardt
myFolderPath = awb.path & "\"
myFilePath = myFolderPath & myEmployee.value & ".xls"
Try using this Sub to open the file
Sub OpenWorksheet(myFilePath As String, oWB As Workbook)
Dim myFilePath As String
Dim ShortName As String
Dim aWB As Workbook
Dim oWB As Workbook
Set aWB = ActiveWorkbook
'Opens Finance workbook
ShortName = Right(myFilePath, Len(myFilePath) - InStrRev(myFilePath, "\"))
On Error Resume Next
Set oWB = Nothing
Set oWB = Workbooks(ShortName)
If oWB Is Nothing Then
Set oWB = Workbooks.Open(myFilePath)
End If
On Error GoTo 0
End Sub
And No, I didn't create all this from scratch. I had used something like
this before. :)
--
HTH,
Barb Reinhardt
The code you have given seems to be a very efficient one. I may have
to figure out how to call that sub to open/close my files. I am still
trying on a logic as to how do I compare both the numbers in master
and employee files for each and every date and employee. Will keep you
posted.
Thank you
On Oct 3, 11:36 pm, Barb Reinhardt