PDA

View Full Version : For loop help



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

mdmackillop
04-01-2013, 09:26 AM
Can you post a sample workbook? Use Manage Attachments in the Go Advanced reply section.

ekh5035
04-01-2013, 09:46 AM
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!

sassora
04-01-2013, 03:14 PM
I think I understand what you are looking for:

How about an INDEX/MATCH using an array formula?

=INDEX(Sheet1!A:A,MATCH($G2&LEFT($H2,5),Sheet2!$F:$F&LEFT(Sheet2!$C:$C,5),0))
It needs a ctrl-shift and Enter

This would be best calculated in VBA along with a list of unique numbers and names in sheet 3.

See the attached file, this extracts the relevant value from column A in Sheet 1 into Sheet 3.

Paul_Hossler
04-01-2013, 05:17 PM
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


'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

RowCountMster = wsNumber.Cells(1, 1).CurrentRegion.Rows.Count
RowCountInc = wsName.Cells(1, 1).CurrentRegion.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

'Sheets(3).Select
'Cells(1, 1).Select
'Selection.End(xlDown).Select

'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

wsMatch.Cells(PasteRow, 1).FormulaR1C1 = District
wsMatch.Cells(PasteRow, 2).FormulaR1C1 = Office
wsMatch.Cells(PasteRow, 3).FormulaR1C1 = Route
wsMatch.Cells(PasteRow, 4).FormulaR1C1 = Miles
wsMatch.Cells(PasteRow, 5).FormulaR1C1 = Dte
wsMatch.Cells(PasteRow, 6).FormulaR1C1 = Amount
wsMatch.Cells(PasteRow, 7).FormulaR1C1 = FinanceNo
wsMatch.Cells(PasteRow, 8).FormulaR1C1 = Name

End If
sheet3count = 0
End If
Next n
Next i

Application.ScreenUpdating = True

End Sub


Paul

ekh5035
04-08-2013, 08:35 AM
Paul,

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.

sassora
04-08-2013, 09:55 AM
Glad you found it helpful.

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.

Sassora

Paul_Hossler
04-08-2013, 12:15 PM
Option Explict really means that all variables must be Dim-ed

Without explicitly Dim-ing, then a typo would generate a hard to track down bug:

Simple example --

Without Option Explicit, the If below will never be executed.

With Option Explicit, there will be a design-time error that you can correct as you write your macro



iMonth = 6

if iMnth = 6 Then

......




Paul