ekh5035
04-01-2013, 08:14 AM
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:
RowCountMster = Sheets(1).UsedRange.Rows.Count
RowCountInc = Sheets(2).UsedRange.Rows.Count
RowCountMster = 73093
RowCountInc = 660
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:
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
RowCountMster = Sheets(1).UsedRange.Rows.Count
RowCountInc = Sheets(2).UsedRange.Rows.Count
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
Sheets(3).Select
Cells(1, 1).Select
Selection.End(xlDown).Select
PasteRow = Selection.Row
Sheets(3).Cells(PasteRow + 1, 1).FormulaR1C1 = District
Sheets(3).Cells(PasteRow + 1, 2).FormulaR1C1 = Office
Sheets(3).Cells(PasteRow + 1, 3).FormulaR1C1 = Route
Sheets(3).Cells(PasteRow + 1, 4).FormulaR1C1 = Miles
Sheets(3).Cells(PasteRow + 1, 5).FormulaR1C1 = Dte
Sheets(3).Cells(PasteRow + 1, 6).FormulaR1C1 = Amount
Sheets(3).Cells(PasteRow + 1, 7).FormulaR1C1 = FinanceNo
Sheets(3).Cells(PasteRow + 1, 8).FormulaR1C1 = Name
End If
sheet3count = 0
End If
Next n
Next i
Application.ScreenUpdating = True
End Sub
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:
RowCountMster = Sheets(1).UsedRange.Rows.Count
RowCountInc = Sheets(2).UsedRange.Rows.Count
RowCountMster = 73093
RowCountInc = 660
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:
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
RowCountMster = Sheets(1).UsedRange.Rows.Count
RowCountInc = Sheets(2).UsedRange.Rows.Count
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
Sheets(3).Select
Cells(1, 1).Select
Selection.End(xlDown).Select
PasteRow = Selection.Row
Sheets(3).Cells(PasteRow + 1, 1).FormulaR1C1 = District
Sheets(3).Cells(PasteRow + 1, 2).FormulaR1C1 = Office
Sheets(3).Cells(PasteRow + 1, 3).FormulaR1C1 = Route
Sheets(3).Cells(PasteRow + 1, 4).FormulaR1C1 = Miles
Sheets(3).Cells(PasteRow + 1, 5).FormulaR1C1 = Dte
Sheets(3).Cells(PasteRow + 1, 6).FormulaR1C1 = Amount
Sheets(3).Cells(PasteRow + 1, 7).FormulaR1C1 = FinanceNo
Sheets(3).Cells(PasteRow + 1, 8).FormulaR1C1 = Name
End If
sheet3count = 0
End If
Next n
Next i
Application.ScreenUpdating = True
End Sub