Okay so i'm running a program that is matching data from two worksheets. The two worksheets have two columns that can be matched; finance number and name. What i'm trying to do is match the finance number between the sheets, and then match the last four characters of the last name (i'd match the entire name, but the way the name's are entered is different so it has to be the last four characters of the last name). Once the program finds a matching finance number and name it pastes data (to a 3rd worksheet) from multiple columns from both sheets of the given rows that contain the match.
I believe i'm having trouble with the four loops right now. If I run the code with the for loops limited to 50 rows, then the program runs fine and pastes the data to the third sheet. When running the program with the intended for loop ranges I get nothing from the program and excel will not respond for about 5 minutes before the program stops and nothing has changed.
My for loop range is determined by these variables:
When limiting these variables to 50 the program works.
Is there a limit to the range of for loops? My variable are dim as long, is there any other way to dim them? Is this the worst possible way to do this? Any other help or suggestions would be much appreciated.
Code:
[VBA]Sub MatchandSort()
Application.ScreenUpdating = False
Dim MsterFin, IncFin, MsterName4, IncName4, IncNames, MsterNames As String
Dim i, n, sheet3count, x, PasteRow As Long
Dim District, Office, Route, Miles, Dte, Amount, FinanceNo, Name As String
Dim RowCountMster, RowCountInc As Long
Sheets(2).Select
For i = 2 To RowCountInc
IncFin = Cells(i, 6).Value
Sheets(1).Select
For n = 2 To RowCountMster
MsterFin = Cells(n, 5).Value
If MsterFin = IncFin Then
MsterNames = Right(Cells(n, 36).Value, 5)
Sheets(2).Select
IncNames = Right(Cells(i, 3).Value, 5)
If MsterNames = IncNames Then
District = Sheets(1).Cells(n, 4).Value
Office = Sheets(1).Cells(n, 6).Value
Route = Sheets(1).Cells(n, 8).Value
Miles = Sheets(1).Cells(n, 22).Value
Dte = Sheets(2).Cells(i, 8).Value
Amount = Sheets(2).Cells(i, 7).Value
FinanceNo = Sheets(2).Cells(i, 6).Value
Name = Sheets(2).Cells(i, 3).Value
Attached you will find a sample workbook. Sheet 1 will have data down 73093 rows and sheet 2 will have data at 660 rows. If this sample is not detailed enough please let me know and I'll upload something better. Thanks for your help!
A VBA approach -- I added some (hopefully) suggestions, but a lot is personal style.
The loops 'seem' to work quick, but not sure about the logic
[vba]
'You might have had Option Explicit, but I didn't see it
'Usually a good idea
Option Explicit
Sub MatchandSort()
'IMHO easier to use var
Dim wsNumber As Worksheet, wsName As Worksheet, wsMatch As Worksheet
Application.ScreenUpdating = False
'the only one that is a real string is the last one
'the rest are all of Variant type since there's no ... As ...
'same for the other Dim lines
'I just made assumption that the last was the intended type, but there could be dates, money, etc.
'Dim MsterFin, IncFin, MsterName4, IncName4, IncNames, MsterNames As String
Dim MsterFin As String, IncFin As String, MsterName4 As String, IncName4 As String, IncNames As String, MsterNames As String
Dim i As Long, n As Long, sheet3count As Long, x As Long, PasteRow As Long
Dim District As String, Office As String, Route As String, Miles As String, Dte As String, Amount As String, FinanceNo As String, Name As String
Dim RowCountMster As Long, RowCountInc As Long
Set wsNumber = ActiveWorkbook.Worksheets("Sheet1")
Set wsName = ActiveWorkbook.Worksheets("Sheet2")
Set wsMatch = ActiveWorkbook.Worksheets("Sheet3")
'used range includes cells that have ever been used (can be very many)
'including those that look empty. I find that .CurrentRegion is usually enough
'RowCountMster = Sheets(1).UsedRange.Rows.Count
'RowCountInc = Sheets(2).UsedRange.Rows.Count
'no need to select if you're explicit
'Sheets(2).Select
For i = 2 To RowCountInc
IncFin = wsName.Cells(i, 6).Value
'no need to select if you're explicit
'Sheets(1).Select
For n = 2 To RowCountMster
MsterFin = wsNumber.Cells(n, 5).Value
If MsterFin = IncFin Then
MsterNames = Right(wsNumber.Cells(n, 36).Value, 5)
'Sheets(2).Select
IncNames = UCase(Right(wsName.Cells(i, 3).Value, 5))
'your example had Smith and smith
If UCase(MsterNames) = UCase(IncNames) Then
District = wsNumber.Cells(n, 4).Value
Office = wsNumber.Cells(n, 6).Value
Route = wsNumber.Cells(n, 8).Value
Miles = wsNumber.Cells(n, 22).Value
Dte = wsName.Cells(i, 8).Value
Amount = wsName.Cells(i, 7).Value
FinanceNo = wsName.Cells(i, 6).Value
Name = wsName.Cells(i, 3).Value
'if there's only data in A1, PasteRow is the end
'I prefer to work up from the bottom
'BUT District was blank, so I used col 7
PasteRow = wsMatch.Cells(wsMatch.Rows.Count, 7).End(xlUp).Row + 1
Thanks a lot for your reply, not only did it work perfectly, but it taught me a lot about cleaning up my code. When question though: When using option explicit, is that when you have to dim each variable as the specific variable type (ie. var1 as integer, var2 as integer, var3 as integer, etc)? Versus var1, var2, var3 as integer? Or is this just generally good practice? Thanks!
Sassora,
I also tried your method and it worked just as well. I used the Concatenate to combine the cells I was matching as a type of ID code (first 6 characters are the finance number, then the last four are the last four characters of a last name) on both sheets and then used the index-match to match these codes and pull the corresponding row values. Anyway, appreciate the response, this was a great way to introduce me to some of the built in functions in excel.
Option explicit means you have to define all variables up front. An omission of variable type is taken to indicate "as variant". So Dim var1, var2 as integer looks like shorthand but it means var1 is variant and var2 is integer.
Reply With Quote
