[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Excel 2010: finding duplicates in child sheets and highlighting them in master sheet

I have an excel 2010 workbook (FMG All Good Emails_250K_Apportioned _MB-July16-14.xlsm) comprised of tens of thousands of records and about 20 sheets.

On the master sheet (FMG All Good Emails_July_Apport) there are approx 200,000 records (rows). All the other sheets are approximately 10,000 each and each one of them contain copies of records from the master sheet.

I need a function that highlights the entire row in the master sheet when the email address from that master sheet is found in one of the 'children sheets' (sheets other than the master) and I need the name of the tab inserted in every row in Col O of the master sheet.

For example. I have a sheet named Trevor Turner. The function would find all email addresses in sheet Trevor Turner and locate them in the master sheet (FMG All Good Emails_July_Apport). When a match is found in the master sheet the function would highlight the entire row with a unique color AND put the name Trevor Turner in every row in Col O.

I have the following children sheet:
Claudia Lynch
Darin Swall
David Fields
Dino Restrepo
Fred Schwarz
John Winans
Josh Landis
Larry Demaree
Leandra Hill
Marc Rogers
Mike Fiore
Mike Gordon
Michael Strayhorn
Paul Heffernan
Scott E. Naughton
Steve Katcher
Brooks Boucher
Dan Pederson
Chad Dokken
Trevor Turner
Drew Dokken

Having this would be a great, great help to find who has what in the master list.
If there are duplications (an email found in more than one child list) then that would
be good to note somewhere as well if no match can be found.
0
mabehr
Asked:
mabehr
  • 22
  • 12
1 Solution
 
Ejgil HedegaardCommented:
Try this.
Insert in a module, change the column number in the line
Const colMail As Integer = 3
and run.

Option Explicit

Sub FindEmailMatch()
Dim ws As Worksheet, wsMaster As Worksheet, rw As Long
Dim MailSearch As Variant, MailSheetsMatch As String

'Change below to the column number with the e-mails on the master sheet
Const colMail As Integer = 3
    
    Set wsMaster = ThisWorkbook.Worksheets("FMG All Good Emails_July_Apport")
    wsMaster.Cells.Interior.Color = xlNone
    For rw = 2 To wsMaster.UsedRange.Rows.Count
        MailSheetsMatch = ""
        For Each ws In ThisWorkbook.Worksheets
            If Len(wsMaster.Cells(rw, colMail)) > 0 Then
                If ws.Name <> wsMaster.Name Then
                    Set MailSearch = ws.Cells.Find(What:=wsMaster.Cells(rw, colMail))
                    If Not MailSearch Is Nothing Then
                        If Len(MailSheetsMatch) = 0 Then
                            MailSheetsMatch = ws.Name
                        Else
                            MailSheetsMatch = MailSheetsMatch + ", " + ws.Name
                        End If
                    End If
                End If
            End If
        Next ws
        If MailSheetsMatch = "" Then
            wsMaster.Range(("O" & rw)) = "No Match"
        Else
            wsMaster.Range(("O" & rw)) = MailSheetsMatch
            wsMaster.Rows(rw).EntireRow.Interior.Color = 65535
        End If
    Next rw
End Sub

Open in new window

0
 
mabehrAuthor Commented:
Thanks Ejgil.

I've run this and it seemed to work as it began highlighting rows and inserting names as it should, but then as it continued I couldn't see what it was doing and now it's been running for 14 hours and I don't know if it's stuck or still processing. I let it run overnight and since I can't see I think I need to stop the process (without the ability to save) and probably start over with a smaller number of sheets, maybe one at a time.
0
 
Ejgil HedegaardCommented:
Press Ctrl+Break to stop, and then End.
It is slow to search 200,000 rows on Master, and 10,000 rows * Columns on the other sheets 20 times for each row.
The process can be speeded if the search can be narrowed to only one column with the e-mails, and not the entire sheet.
Also screen updating can be turned off.
I think a process indicator in the statusbar would, showing the row processed, (or update each 100 rows) would be handy as well.
If that is possible, please specify.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mabehrAuthor Commented:
Thanks. How do I speed up the process then? How do I narrow the search for one column for both the master and children sheets?

Not sure what you mean by screen updating, but yes, that would be good. How do I know if it's possible?
0
 
Ejgil HedegaardCommented:
Tell me where the data are, and if all sheets have the same setup, then I will change the macro.
Best to upload a small sample file.
0
 
mabehrAuthor Commented:
Attached is a sample of the workbook layout. All children sheets have the same layout.

 ExampleWorkbook.xlsm
0
 
mabehrAuthor Commented:
And just so you know, there could be blank rows in any of the spreadsheets.
0
 
Ejgil HedegaardCommented:
Here is the sample file with a revised macro.
You can follow the progress in the lower left of the screen.
ExampleWorkbook-Match-e-mail.xlsm
0
 
mabehrAuthor Commented:
Thanks, Ejgil. Now I can see it working. It's going through at about 100 records every 8 seconds. Been going for a few hours. Will go for a few more. Look forward to seeing the results. I'll let you know but not until Monday as it will be whirring away on my work computer over the weekend while I'm at home.
0
 
Ejgil HedegaardCommented:
if 100 records every 8 seconds is average for all, then it will run for 5 hours for 200,000 records.
That is only acceptable for a one time use.

So I have looked into further means to speed up the process.
All mails from child sheets copied to a search sheet, with the sheet name in a column next to.
Then combining all sheet names for equal e-mails into one string and deleting the others
Sorted by emails, and a table made for the start and end row for the first 2 characters in the emails.
Then the Find function only has to search once on a short range.

Tested with some dummy mails, 23,000 rows on master sheet, with 3 child sheets each 10,000 mails, some duplicate on the sheets, total app. 17,000.
The search sheet was made in 10 seconds, and the function then process about 1,000 records per second, so your 200,000 records should be processed in some minutes.
Match-e-mail-ver-2.xlsm
0
 
mabehrAuthor Commented:
Wow! Thanks. I'll try that out when I get to the office later this morning.
0
 
mabehrAuthor Commented:
Wow!! Yes, ran that and it took 4 minutes instead of many hours! Thanks. Very nice.

Now...I have to do something with these duplicates because that is a problem. (I need to remove them from one of the child lists.) If I submit another questions would you be willing to help me with that?
0
 
mabehrAuthor Commented:
On deeper look, it appears that this new process did not identify all from the children's sheets. I did a search for a number of emails from a child sheet and they did not show up as identified in the master sheet. Many were found so it looked like it got them all, but further investigation revealed many were not picked up. I'm still investigating.
0
 
mabehrAuthor Commented:
For example. If I do a sort by Col O (the column where the sheet names are inserted) I only get 18,496 names before I hit the name No Match. However, I have a LOT of names (sheet names) that come before No Match and adding only two of them would get me close to 20,000. So, for some reason this new function is missing a lot.
0
 
mabehrAuthor Commented:
Sorry to bother, Ejgil, but wanted to give you updates as I found them.

I did the same check with the first function you provided and it came up with more, but only 85,604. My estimation with 17 names before "No Match" is that I should have around 170,000 (with approx. 10,000 per sheet). So... it looks like
neither function got all the names matched from all the sheets.

That is unless I'm not seeing something.
0
 
Ejgil HedegaardCommented:
No bother, we have to get it working.
That's what happens when real data is not available.
Some tests are always needed.
Could be the e-mails are "identical", except for upper/lower case characters.
VBA is sensitive to that, Excel is not, so "A" is not identical to "a" in VBA terms.
Difficult to remember everything without real data to test on.
In attached the search sheet is made using upper case match to combine the sheets.
The search itself is not case sensitive, when told not to be.

If possible, upload some sample data, part of master and part of some child sheets.
If it is somewhat sensitive, you can find my e-mail in my profile.
Then the test can be done using Excel formulas.
Match-e-mail-ver-3.xlsm
0
 
mabehrAuthor Commented:
Ran the new code and did a sort based on Col 0 (the sheet name) and this time it got to 77,463 before it hit the "No Match" value. Then it picks up finding matches at 187,444 until the end at 205,188.

So, it is finding matches for 95,206 when I have 205,188 on the master sheet and a total of 195,000 total on the children sheets. I should just have about 10,000 left on the master sheet unclaimed. Of course, as you stated, there are duplicates I need to deal with and that changes the numbers but there are not 100,000 duplicates. I think.

But maybe we should deal with the duplicates first so we can see more precisely how many duplicates there are. (Duplicates are where more than one child sheet is claiming an email address on the master sheet). I could ask another question dealing with duplicates and once that was solved get back  to this one. What do you think?
0
 
Ejgil HedegaardCommented:
I think we have to get the basic functions to work, so lets go on a little further.
To check for duplicates in column O on the master sheet, use this formula in column R.
=LEN(O2)-LEN(SUBSTITUTE(O2,",",""))
When cell R2 is active, copy down = double click on the black square in the lower right of the cell marker.
The value state the number of duplicates in cell O2.
Use the autofilter to filter the values, or a Countif formula to count them.
0
 
mabehrAuthor Commented:
Drats! I can't get the double clicking to auto fill when I double click the black cross. Still trying.
0
 
mabehrAuthor Commented:
Since I'm having to pull this copy down manually it's taking some time and I'm up  to row 69,849 and so far have totaled 14,504 duplicates. Will have to continue tomorrow.
0
 
broro183Commented:
Hi,
I don't have access to Excel at the moment so I can't look at the file but I maybe able to help with your 'copy down' issue.
Copy the first cell using [ctrl + c], is it R2 (?), figure out what the last row is (eg 200,004), press [F5] to bring up the GoTo dialog, type in r2:r200004 press [enter] twice. I think the first time will select the range and close the dialog box and the second time will paste the formula which was copied from cell R2 into the entire range in one go.

Hth
Rob
0
 
mabehrAuthor Commented:
Did that and it worked. Thanks. There are 15,143 duplicates so while I do need to take care of these it obviously does not account for the 100,000 or so that have not been matched up.
0
 
Ejgil HedegaardCommented:
Without some real data, it is difficult to see what is happening.
0
 
mabehrAuthor Commented:
In a few minutes I'm going to send to your email a version of the workbook I am working with. Only email addresses remain and they have all been altered. It's rather big. Please let me know if you receive it.
0
 
mabehrAuthor Commented:
19mb and sent just now.
0
 
mabehrAuthor Commented:
Got anything Ejgil?
0
 
Ejgil HedegaardCommented:
That was a tricky error to solve!

First I did some research on the sheets, using Excel lookup formulas, to make something to compare the program result to.
The child sheets have 206,215 mails, a lot of duplicates (also on same sheet) giving 186,142 different mails.
Some of them start with a space or character 160, probably copied from a web page, have made a clean up for that.
105,221 mails match a mail on the master sheet, but the master sheet has 1 duplicate, so 105,222 mails of the 205,188 on master sheet match a mail on one or more child sheets.


The problem was that sorting the mails does not function correctly.
Bug in Excel?

As an example, the sort is
ahanb
a-hans
ahans

a-hans is between the others starting with ah, instead of before or after.
Try copy the mails from Fred, Mike G, Josh and Michael to one column, sort and search for ahanb.

The first 2 characters are used to make at table, to narrow the search range and speed up the process, so it is critical, because the search range for ah stops at ahanb, a new for a-hans, and another for ah starting at ahans, but the second will not be used.

I made a column with the character code values, to sort on before the mail sort.
It takes a little longer to build the table then, but with the character codes no sorting error.
I could also have looped the table to check for more ranges, but guess it is slower to search twice or more.

The result on master sheet is 105,222 matches.

I will delete the files when we are done.

Here is the code.

Option Explicit
Dim ws As Worksheet, wsMaster As Worksheet, wsSearch As Worksheet
Dim rw As Long, rwMax As Long
Dim MailSearch As Variant, MailSheetsMatch As String, SearchFor As String, SearchForLeft2 As String, SheetName As String, Msg As String
Dim arSearchTable() As Variant, TablePos As Integer, TablePosMax As Integer
Dim rwSearch As Long, rwSearchMax As Long, rwSearchStart As Long, rwSearchEnd As Long
Dim i As Integer, SortRange As Range, txt As String
Const colMail As Integer = 13
Const SheetMasterName As String = "FMG All Good Emails_July_Apport"

Sub FindEmailMatch()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error GoTo ErrorStop
    
    ThisWorkbook.Activate
    Set wsMaster = Worksheets(SheetMasterName)
    wsMaster.Select
    wsMaster.Cells.Interior.Color = xlNone
    rwMax = wsMaster.UsedRange.Rows.Count
    
    Application.StatusBar = "Preparing to search"
    CombineMailsAndSheet
    Application.StatusBar = "Search start"

    wsSearch.Select
    For rw = 2 To rwMax
        If Int(rw / 1000) = rw / 1000 Then
            Application.StatusBar = "Processing row" + Str(rw) + " of" + Str(rwMax)
            DoEvents
        End If
        MailSheetsMatch = ""
        SearchFor = wsMaster.Cells(rw, colMail)
        If Len(SearchFor) > 1 Then
            SearchForLeft2 = WorksheetFunction.Text(Asc(LCase(Left(SearchFor, 1))), "000") + WorksheetFunction.Text(Asc(LCase(Mid(SearchFor, 2, 1))), "000")
            TablePos = 1
            i = 0
            Do
                If arSearchTable(TablePos, 1) = SearchForLeft2 Then
                    rwSearchStart = arSearchTable(TablePos, 2)
                    rwSearchEnd = arSearchTable(TablePos, 3)
                    i = 1
                Else
                    TablePos = TablePos + 1
                End If
            Loop Until i = 1 Or TablePos > TablePosMax
            If TablePos <= TablePosMax Then
                Set MailSearch = wsSearch.Range(Cells(rwSearchStart, 1), Cells(rwSearchEnd, 1)).Find(What:=SearchFor, LookAt:=xlWhole, MatchCase:=False)
                    If Not MailSearch Is Nothing Then
                        MailSheetsMatch = wsSearch.Cells(MailSearch.Row, 2)
                    End If
            End If
        End If
        If MailSheetsMatch = "" Then
            wsMaster.Range(("O" & rw)) = "No Match"
        Else
            wsMaster.Range(("O" & rw)) = MailSheetsMatch
        End If
    Next rw
    wsMaster.Select
    For rw = 2 To rwMax
        If Int(rw / 1000) = rw / 1000 Then
            Application.StatusBar = "Highlight matches, row" + Str(rw) + " of" + Str(rwMax)
            DoEvents
        End If

        If wsMaster.Range(("O" & rw)) <> "No Match" Then
            wsMaster.Range(Cells(rw, 1), Cells(rw, 15)).Interior.Color = 65535
        End If
    Next rw
    Application.DisplayAlerts = False
    wsSearch.Delete
    Application.DisplayAlerts = True


ErrorStop:
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    
    If Err.Number <> 0 Then
        Msg = "Error in row" + Str(rw) + Chr(10) + "Error # " + Str(Err.Number) + Chr(10) + Err.Description + Chr(10) + "Program stop"
        MsgBox Msg, , "Error"
    Else
        Msg = "Mail match completed"
    End If
End Sub

Private Sub CombineMailsAndSheet()
    i = 0
    For Each ws In Worksheets
        If ws.Name = "SearchSheet" Then
            i = 1
            Set wsSearch = ws
            wsSearch.Cells.Clear
        End If
    Next ws
    If i = 0 Then
        Set wsSearch = Worksheets.Add
        wsSearch.Name = "SearchSheet"
    End If
    wsSearch.Range("A1") = "Mail"
    wsSearch.Range("B1") = "Sheet"
    wsSearch.Range("C1") = "Code"
    wsSearch.Range("D1") = "Use"
    rwSearchMax = 1
    For Each ws In Worksheets
        If ws.Name <> wsMaster.Name And ws.Name <> wsSearch.Name Then
            Application.StatusBar = "Preparing to search, copy data from sheet " + ws.Name
            ws.Select
            ws.Range("C2:C" & ws.UsedRange.Rows.Count).Copy
            wsSearch.Select
            wsSearch.Range(("A" & (rwSearchMax + 1))).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            rwSearch = rwSearchMax + 1
            rwSearchMax = wsSearch.UsedRange.Rows.Count
            wsSearch.Range(Cells(rwSearch, 2), Cells(rwSearchMax, 2)) = ws.Name
        End If
    Next ws
    Application.StatusBar = "Preparing to search, table values"
    Set SortRange = wsSearch.Range(Cells(1, 1), Cells(rwSearchMax, 2))
    SortRange.Sort Key1:="Mail", Order1:=xlAscending, Header:=xlYes

    rwSearch = wsSearch.Range(("A1:A" & wsSearch.Range("A1").End(xlDown).Row)).Rows.Count
    wsSearch.Range(Cells(rwSearch + 1, 1), Cells(rwSearchMax, 2)).Delete
    rwSearchMax = rwSearch
    wsSearch.Columns(3).NumberFormat = "@"
    For rwSearch = 2 To rwSearchMax
        If Int(rwSearch / 1000) = rwSearch / 1000 Then
            Application.StatusBar = "Preparing to search, table values, row " + Str(rwSearch) + " of" + Str(rwSearchMax)
            DoEvents
        End If

        txt = wsSearch.Cells(rwSearch, 1)
        If Len(txt) < 2 Then
            wsSearch.Cells(rwSearch, 3) = "999999"
        Else
            If Len(txt) <> Len(Trim(txt)) Then
                wsSearch.Cells(rwSearch, 1) = Trim(txt)
            End If
            If Left(txt, 1) = Chr(160) Then
                wsSearch.Cells(rwSearch, 1) = Right(txt, Len(txt) - 1)
            End If
            wsSearch.Cells(rwSearch, 3) = WorksheetFunction.Text(Asc(LCase(Left(wsSearch.Cells(rwSearch, 1), 1))), "000") + WorksheetFunction.Text(Asc(LCase(Mid(wsSearch.Cells(rwSearch, 1), 2, 1))), "000")
        End If
    Next rwSearch
    Set SortRange = wsSearch.Range(Cells(1, 1), Cells(rwSearchMax, 3))
    SortRange.Sort Key1:="Code", Order1:=xlAscending, Key2:="Mail", Order1:=xlAscending, Header:=xlYes

    For rwSearch = 2 To rwSearchMax
        If Int(rwSearch / 1000) = rwSearch / 1000 Then
            Application.StatusBar = "Preparing to search, combine, row " + Str(rwSearch) + " of" + Str(rwSearchMax)
        End If

        If LCase(wsSearch.Cells(rwSearch, 1)) = LCase(wsSearch.Cells(rwSearch + 1, 1)) Then
            wsSearch.Cells(rwSearch, 4) = 2
        Else
            wsSearch.Cells(rwSearch, 4) = 1
        End If
        If LCase(wsSearch.Cells(rwSearch, 1)) = LCase(wsSearch.Cells(rwSearch - 1, 1)) Then
            wsSearch.Cells(rwSearch, 2) = wsSearch.Cells(rwSearch - 1, 2) & ", " & wsSearch.Cells(rwSearch, 2)
        End If
    Next rwSearch

    Set SortRange = wsSearch.Range(Cells(1, 1), Cells(rwSearchMax, 4))
    SortRange.Sort Key1:="Use", Order1:=xlAscending, Header:=xlYes
    rwSearch = wsSearch.Range(Cells(2, 4), Cells(rwSearchMax, 4)).Find(What:="2").Row
    wsSearch.Range(Cells(rwSearch, 1), Cells(rwSearchMax, 4)).Delete
    rwSearchMax = rwSearch - 1

    Application.StatusBar = "Preparing to search, searchrange table"
    TablePosMax = 0
    For rwSearch = 2 To rwSearchMax
        If wsSearch.Cells(rwSearch, 3) <> wsSearch.Cells(rwSearch - 1, 3) Then
            TablePosMax = TablePosMax + 1
        End If
    Next rwSearch
    ReDim arSearchTable(1 To TablePosMax, 1 To 3)
    TablePos = 0
    For rwSearch = 2 To rwSearchMax
        If Int(rwSearch / 1000) = rwSearch / 1000 Then
            Application.StatusBar = "Preparing to search, searchrange table, row " + Str(rwSearch) + " of" + Str(rwSearchMax)
            DoEvents
        End If
        
        If wsSearch.Cells(rwSearch, 3) <> wsSearch.Cells(rwSearch - 1, 3) Then
            TablePos = TablePos + 1
            arSearchTable(TablePos, 1) = wsSearch.Cells(rwSearch, 3)
            arSearchTable(TablePos, 2) = rwSearch
        End If
        If wsSearch.Cells(rwSearch, 3) <> wsSearch.Cells(rwSearch + 1, 3) Then
            arSearchTable(TablePos, 3) = rwSearch
        End If
    Next rwSearch
End Sub

Open in new window

0
 
mabehrAuthor Commented:
Thanks Ejgil. I'll try this out shortly. I'm shocked there were so many duplicates. Anyway, thanks. I'll get back to you on this.
0
 
mabehrAuthor Commented:
Ejgil, I'm able to work with this now. Does your code do anything with duplicates found on any of the sheets?
0
 
Ejgil HedegaardCommented:
The duplicates are combined for all mails on the child sheets, also when duplicates on same sheet.
See column O row 5, where the mail is found twice on Brooks, and once on Chad

But when the code finish, only mails on master sheet are shown.

To see the sheets for all child mails set a ' in front of line 73 "wsSearch.Delete", to make it a comment, doing nothing. The line turns green.

Then the sheet "SearchSheet" is not deleted, and you can see the child mails in column A, and the child sheet names in column B.
Delete column C and D.
0
 
mabehrAuthor Commented:
OK, let's call this problem solved. Thank you very much.

Now, I need to move on to the next problem with this workbook and that is to remove the duplicates from both the master sheet and all the child sheets. If I submit this question would you be willing to tackle this one?
0
 
Ejgil HedegaardCommented:
Sure, but others can participate as well, as is the nature for EE.

Refer to this question, so others can see what has been done.

Specify if the sheets must be sorted before the elimination of the duplicates, easiest way to do it, but if not, that is possible as well.
0
 
mabehrAuthor Commented:
Are you there, Elgil?
For some reason I'm now getting an error when I run this. It is attached and all the code in the module is below:
error
Option Explicit
Dim ws As Worksheet, wsMaster As Worksheet, wsSearch As Worksheet
Dim rw As Long, rwMax As Long
Dim MailSearch As Variant, MailSheetsMatch As String, SearchFor As String, SearchForLeft2 As String, SheetName As String, Msg As String
Dim arSearchTable() As Variant, TablePos As Integer, TablePosMax As Integer
Dim rwSearch As Long, rwSearchMax As Long, rwSearchStart As Long, rwSearchEnd As Long
Dim i As Integer, SortRange As Range, txt As String
Const colMail As Integer = 13
Const SheetMasterName As String = "FMG All Good Emails_July_Apport"

Sub FindEmailMatch()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    On Error GoTo ErrorStop
    
    ThisWorkbook.Activate
    Set wsMaster = Worksheets(SheetMasterName)
    wsMaster.Select
    wsMaster.Cells.Interior.Color = xlNone
    rwMax = wsMaster.UsedRange.Rows.Count
    
    Application.StatusBar = "Preparing to search"
    CombineMailsAndSheet
    Application.StatusBar = "Search start"

    wsSearch.Select
    For rw = 2 To rwMax
        If Int(rw / 1000) = rw / 1000 Then
            Application.StatusBar = "Processing row" + Str(rw) + " of" + Str(rwMax)
            DoEvents
        End If
        MailSheetsMatch = ""
        SearchFor = wsMaster.Cells(rw, colMail)
        If Len(SearchFor) > 1 Then
            SearchForLeft2 = WorksheetFunction.Text(Asc(LCase(Left(SearchFor, 1))), "000") + WorksheetFunction.Text(Asc(LCase(Mid(SearchFor, 2, 1))), "000")
            TablePos = 1
            i = 0
            Do
                If arSearchTable(TablePos, 1) = SearchForLeft2 Then
                    rwSearchStart = arSearchTable(TablePos, 2)
                    rwSearchEnd = arSearchTable(TablePos, 3)
                    i = 1
                Else
                    TablePos = TablePos + 1
                End If
            Loop Until i = 1 Or TablePos > TablePosMax
            If TablePos <= TablePosMax Then
                Set MailSearch = wsSearch.Range(Cells(rwSearchStart, 1), Cells(rwSearchEnd, 1)).Find(What:=SearchFor, LookAt:=xlWhole, MatchCase:=False)
                    If Not MailSearch Is Nothing Then
                        MailSheetsMatch = wsSearch.Cells(MailSearch.Row, 2)
                    End If
            End If
        End If
        If MailSheetsMatch = "" Then
            wsMaster.Range(("O" & rw)) = "No Match"
        Else
            wsMaster.Range(("O" & rw)) = MailSheetsMatch
        End If
    Next rw
    wsMaster.Select
    For rw = 2 To rwMax
        If Int(rw / 1000) = rw / 1000 Then
            Application.StatusBar = "Highlight matches, row" + Str(rw) + " of" + Str(rwMax)
            DoEvents
        End If

        If wsMaster.Range(("O" & rw)) <> "No Match" Then
            wsMaster.Range(Cells(rw, 1), Cells(rw, 15)).Interior.Color = 65535
        End If
    Next rw
    Application.DisplayAlerts = False
    'wsSearch.Delete
    Application.DisplayAlerts = True


ErrorStop:
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    
    If Err.Number <> 0 Then
        Msg = "Error in row" + Str(rw) + Chr(10) + "Error # " + Str(Err.Number) + Chr(10) + Err.Description + Chr(10) + "Program stop"
        MsgBox Msg, , "Error"
    Else
        Msg = "Mail match completed"
    End If
End Sub

Private Sub CombineMailsAndSheet()
    i = 0
    For Each ws In Worksheets
        If ws.Name = "SearchSheet" Then
            i = 1
            Set wsSearch = ws
            wsSearch.Cells.Clear
        End If
    Next ws
    If i = 0 Then
        Set wsSearch = Worksheets.Add
        wsSearch.Name = "SearchSheet"
    End If
    wsSearch.Range("A1") = "Mail"
    wsSearch.Range("B1") = "Sheet"
    wsSearch.Range("C1") = "Code"
    wsSearch.Range("D1") = "Use"
    rwSearchMax = 1
    For Each ws In Worksheets
        If ws.Name <> wsMaster.Name And ws.Name <> wsSearch.Name Then
            Application.StatusBar = "Preparing to search, copy data from sheet " + ws.Name
            ws.Select
            ws.Range("C2:C" & ws.UsedRange.Rows.Count).Copy
            wsSearch.Select
            wsSearch.Range(("A" & (rwSearchMax + 1))).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            rwSearch = rwSearchMax + 1
            rwSearchMax = wsSearch.UsedRange.Rows.Count
            wsSearch.Range(Cells(rwSearch, 2), Cells(rwSearchMax, 2)) = ws.Name
        End If
    Next ws
    Application.StatusBar = "Preparing to search, table values"
    Set SortRange = wsSearch.Range(Cells(1, 1), Cells(rwSearchMax, 2))
    SortRange.Sort Key1:="Mail", Order1:=xlAscending, Header:=xlYes

    rwSearch = wsSearch.Range(("A1:A" & wsSearch.Range("A1").End(xlDown).Row)).Rows.Count
    wsSearch.Range(Cells(rwSearch + 1, 1), Cells(rwSearchMax, 2)).Delete
    rwSearchMax = rwSearch
    wsSearch.Columns(3).NumberFormat = "@"
    For rwSearch = 2 To rwSearchMax
        If Int(rwSearch / 1000) = rwSearch / 1000 Then
            Application.StatusBar = "Preparing to search, table values, row " + Str(rwSearch) + " of" + Str(rwSearchMax)
            DoEvents
        End If

        txt = wsSearch.Cells(rwSearch, 1)
        If Len(txt) < 2 Then
            wsSearch.Cells(rwSearch, 3) = "999999"
        Else
            If Len(txt) <> Len(Trim(txt)) Then
                wsSearch.Cells(rwSearch, 1) = Trim(txt)
            End If
            If Left(txt, 1) = Chr(160) Then
                wsSearch.Cells(rwSearch, 1) = Right(txt, Len(txt) - 1)
            End If
            wsSearch.Cells(rwSearch, 3) = WorksheetFunction.Text(Asc(LCase(Left(wsSearch.Cells(rwSearch, 1), 1))), "000") + WorksheetFunction.Text(Asc(LCase(Mid(wsSearch.Cells(rwSearch, 1), 2, 1))), "000")
        End If
    Next rwSearch
    Set SortRange = wsSearch.Range(Cells(1, 1), Cells(rwSearchMax, 3))
    SortRange.Sort Key1:="Code", Order1:=xlAscending, Key2:="Mail", Order1:=xlAscending, Header:=xlYes

    For rwSearch = 2 To rwSearchMax
        If Int(rwSearch / 1000) = rwSearch / 1000 Then
            Application.StatusBar = "Preparing to search, combine, row " + Str(rwSearch) + " of" + Str(rwSearchMax)
        End If

        If LCase(wsSearch.Cells(rwSearch, 1)) = LCase(wsSearch.Cells(rwSearch + 1, 1)) Then
            wsSearch.Cells(rwSearch, 4) = 2
        Else
            wsSearch.Cells(rwSearch, 4) = 1
        End If
        If LCase(wsSearch.Cells(rwSearch, 1)) = LCase(wsSearch.Cells(rwSearch - 1, 1)) Then
            wsSearch.Cells(rwSearch, 2) = wsSearch.Cells(rwSearch - 1, 2) & ", " & wsSearch.Cells(rwSearch, 2)
        End If
    Next rwSearch

    Set SortRange = wsSearch.Range(Cells(1, 1), Cells(rwSearchMax, 4))
    SortRange.Sort Key1:="Use", Order1:=xlAscending, Header:=xlYes
    rwSearch = wsSearch.Range(Cells(2, 4), Cells(rwSearchMax, 4)).Find(What:="2").Row
    wsSearch.Range(Cells(rwSearch, 1), Cells(rwSearchMax, 4)).Delete
    rwSearchMax = rwSearch - 1

    Application.StatusBar = "Preparing to search, searchrange table"
    TablePosMax = 0
    For rwSearch = 2 To rwSearchMax
        If wsSearch.Cells(rwSearch, 3) <> wsSearch.Cells(rwSearch - 1, 3) Then
            TablePosMax = TablePosMax + 1
        End If
    Next rwSearch
    ReDim arSearchTable(1 To TablePosMax, 1 To 3)
    TablePos = 0
    For rwSearch = 2 To rwSearchMax
        If Int(rwSearch / 1000) = rwSearch / 1000 Then
            Application.StatusBar = "Preparing to search, searchrange table, row " + Str(rwSearch) + " of" + Str(rwSearchMax)
            DoEvents
        End If
        
        If wsSearch.Cells(rwSearch, 3) <> wsSearch.Cells(rwSearch - 1, 3) Then
            TablePos = TablePos + 1
            arSearchTable(TablePos, 1) = wsSearch.Cells(rwSearch, 3)
            arSearchTable(TablePos, 2) = rwSearch
        End If
        If wsSearch.Cells(rwSearch, 3) <> wsSearch.Cells(rwSearch + 1, 3) Then
            arSearchTable(TablePos, 3) = rwSearch
        End If
    Next rwSearch
End Sub
                                          

Option Explicit
Dim ws As Worksheet, wsCsv As Worksheet
Dim rwMax As Long, rwStart As Long, rwEnd As Long, colMax As Integer
Dim FileNbr As Integer, FileNbrMax As Integer
Dim CsvFileName As String

Const MaxRowsInFiles As Long = 1000

Sub MakeCsvFiles()
    ChDrive Left(ThisWorkbook.Path, 2)
    ChDir ThisWorkbook.Path
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        rwMax = ws.Range("A1").CurrentRegion.Rows.Count
        colMax = ws.Range("A1").CurrentRegion.Columns.Count
        FileNbrMax = Int(rwMax / (MaxRowsInFiles - 1)) + 1
        rwStart = 2
        For FileNbr = 1 To FileNbrMax
            CsvFileName = ws.Name + " "
            If FileNbr < 100 Then CsvFileName = CsvFileName + "0"
            If FileNbr < 10 Then CsvFileName = CsvFileName + "0"
            CsvFileName = CsvFileName + Trim(Str(FileNbr))
            rwEnd = rwStart + MaxRowsInFiles - 2
            If rwEnd > rwMax Then
                rwEnd = rwMax
            End If
            Set wsCsv = Worksheets.Add
            If Len(CsvFileName) > 31 Then
                wsCsv.Name = Left(CsvFileName, 27) + Right(CsvFileName, 4)
            Else
                wsCsv.Name = CsvFileName
            End If
            
            ws.Select
            ws.Range(Cells(1, 1), Cells(1, colMax)).Copy
            wsCsv.Select
            wsCsv.Range("A1").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            ws.Select
            ws.Range(Cells(rwStart, 1), Cells(rwEnd, colMax)).Copy
            wsCsv.Select
            wsCsv.Range("A2").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            wsCsv.Move
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=CsvFileName, FileFormat:=xlCSV
            Application.DisplayAlerts = True
            ActiveWorkbook.Close Savechanges:=False
            rwStart = rwEnd + 1
        Next FileNbr
    Next ws
End Sub
                                          

Open in new window

0
 
Ejgil HedegaardCommented:
Looks like you have combined 2 modules into 1.

The error message refers to the code just left of the message.
Option Explicit must be at the top of the module, before any subs and declarations.
Also the Dim (and Const) statements at module level (=outside procedures used for all procedures in the module) must be before any subs in the module.
0
 
mabehrAuthor Commented:
Ok, thanks. Fixed that and it now works. Appreciate the help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 22
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now