Complex loop is not working for 3 worksheets

[DThib]

Help, please
I have code that I am trying to workout that will pull a match from two worksheets, then confirm in one of these that a value is found and then add data to a third sheet in the same workbook.

It is not working and the latest attempt is below.

Any ideas?

DThib

Sub DataRun()


    'Dim MBs, Coos, QAs As Worksheets
    Dim MBRow, CoRow, QARow As Long
    Dim i, j, k As Long


     
    'MBs = Sheets("MB51_Draw")
    'Coos = Sheets("COOIS_Draw")
    'QAws = Sheets("QA_Data")
     
    MBRow = Sheets("MB51_Draw").Cells(Rows.Count, "A").End(xlUp).Row
    CoRow = Sheets("COOIS_Draw").Cells(Rows.Count, "A").End(xlUp).Row
    QARow = Sheets("QA_Data").Cells(Rows.Count, "A").End(xlUp).Row
    
    i = 1 + MBRow
    j = 1 + QARow
    k = CoRow
   For k = 1 To CoRow
     
    If Sheets("COOIS_Draw").Cells(k, 1).Value = Sheets("MB51_Draw").Cells(i, 1).Value And _
      Sheets("MB51_Draw").Cells(i, 7).Value = "543" Then
      
       Sheets("QA_Data").Cells(j, 1).Value = Sheets("COOIS_Draw").Cells(k, 4).Value
    End If
   Next
   
End Sub

 

[gallen]

Not really enough information. What results do you expect and what are you getting?

Do you know which line is the issue?

I can see an issue with your variable declaration:

[COLOR=#333333]    Dim MBRow, CoRow, QARow As Long

[/COLOR][COLOR=#333333]    Dim i, j, k As Long[/COLOR]

This results in 'QARow' and 'k' as longs. The others are variants as you don't state what type they are. My guess is you mean

    Dim MBRow As Long, CoRow As Long, QARow As Long

    Dim i As Long, j As Long, k As Long

 

[DThib]

Hello Gallen,

Thanks for the quick reply.
Origin of data:
I pull the data for the two worksheets from two workbooks that are files from a database.
Each generates a worksheet I copy into the workbook I am creating.
The data has an ID in two different columns depending on the worksheet. I am looking to match that ID up first.

The resulting data also has a confirmation code (543) in another column. I want to make sure the result from the first match has this match as well.

If that is the case then copy the Value from COOIS_Draw to the QA_Data worksheet.

I may be short cutting the declarations as you stated, would that result in no answer?

 

[DThib]

Just tried the suggestion above, it did not work.

Any other ideas?

DThib

 

[gallen]

My suggestions weren't given a solution to your problem but simply pointing out a potential difference in what you expected.

I pull the data for the two worksheets from two workbooks that are files from a database.

There's no mention of 2 workbooks in your OP. I think your commented lines were going in the right direction It's easier when working with several sheets (especially from different workbooks) to use defined variables. Your code failed because you didn't use the keyword "SET", and you didn't declare them as 'Worksheet' although you did declare 1 of them as 'Worksheets' which is a collection object containing 1 or more worksheet.

Can you provide the workbook name of each of the 3 sheets?

I've just read through your code line by line and it is very confused code. We need to go through each line, explain what we the line to do in plain english and then ensure the syntax is correct.

I started it but then got very confused....

Here's where I got to.

Sub DataRun()
    'store the 3 worksheets in variables
    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    'variables to hold row numbers
    Dim MBRow As Long, CoRow As Long, QARow As Long
    'i & j store the last row number + 1 (next empty row) k is used as counter...
    Dim i As Long, j As Long, k As Long




    'fully qualify each sheet bu providing workbook name and worksheet name
    Set MBs = Workbooks("INSERTWORKBOOKNAME").Sheets("MB51_Draw")
    Set Coos = Workbooks("INSERTWORKBOOKNAME").Sheets("COOIS_Draw")
    Set QAws = Workbooks("INSERTWORKBOOKNAME").Sheets("QA_Data")
    
    'get last used row in column A of MB51_Draw
    MBRow = MBs.Cells(Rows.Count, "A").End(xlUp).Row
    'Get last row in COOIS_Draw
    CoRow = Coos.Cells(Rows.Count, "A").End(xlUp).Row
    'Get Last row in QA_Data
    QARow = QAws.Cells(Rows.Count, "A").End(xlUp).Row
    
    'no need to do this, just add 1 to the previous 3 lines and use those variables.
    i = 1 + MBRow
    j = 1 + QARow
    k = CoRow ' why set this when it is being used as a counter in the very next line?
    
    '***k = 1 to CoRow and yet in the previous line k was set to equal CoRow...
   For k = 1 To CoRow
     
    If Coos.Cells(k, 1).Value = MBs.Cells(i, 1).Value And _
      MBs.Cells(i, 7).Value = "543" Then
      
       QAws.Cells(j, 1).Value = Coos.Cells(k, 4).Value
    End If
   Next
   
End Sub

 

[DThib]

Thank you Gallen,

I am sure I've confused myself trying to get this to work.

All entities are in the same workbook(NH_01Jul19)
I've tweaked with the fix and it is still not recognizing what it needs. Your edits are included below:

Sub Newt()


 'store the 3 worksheets in variables
    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    'variables to hold row numbers
    Dim MBRow As Long, CoRow As Long, QARow As Long
    'i & j store the last row number + 1 (next empty row) k is used as counter...
    Dim i As Long, j As Long, k As Long


    'fully qualify each sheet bu providing workbook name and worksheet name
    Set MBs = ThisWorkbook.Sheets("MB51_Draw")
    Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
    Set QAws = ThisWorkbook.Sheets("QA_Data")
    
    'get last used row in column A of MB51_Draw
    MBRow = MBs.Cells(Rows.Count, "A").End(xlUp).Row
    'Get last row in COOIS_Draw
    CoRow = Coos.Cells(Rows.Count, "A").End(xlUp).Row
    'Get Last row in QA_Data
    QARow = QAws.Cells(Rows.Count, "A").End(xlUp).Row
    
    'no need to do this, just add 1 to the previous 3 lines and use those variables.
    i = 1 + MBRow
    j = 1 + QARow
    k = CoRow ' why set this when it is being used as a counter in the very next line?
    
    '***k = 1 to CoRow and yet in the previous line k was set to equal CoRow...
   For k = 1 To CoRow
     
    If Coos.Cells(k, 1).Value = MBs.Cells(i, 13).Value Then
      If MBs.Cells(i, 9).Value = "PTS2" Then
      
       QAws.Cells(j, 1).Value = Coos.Cells(k, 4).Value
      End If
    End If
   Next
   
End Sub

 

[gallen]

OK The functional part is only this code, and it doesn't work. Put a comment above each line, similar to what I did earlier, explaining in plain english what you expect each line to do.

[COLOR=#574123]   For k = 1 To CoRow[/COLOR]     
    If Coos.Cells(k, 1).Value = MBs.Cells(i, 13).Value Then
      If MBs.Cells(i, 9).Value = "PTS2" Then
       QAws.Cells(j, 1).Value = Coos.Cells(k, 4).Value
      End If
    End If [COLOR=#574123]   Next[/COLOR]

 

[DThib]

will do

 

[DThib]

Replying in plain American....

I have included the commented code below.
In brief, the 2 If logic statements define the row results for matches. 1st one finds the match for the ID, the second defines the possible multiple rows to the one with a column result of PTS2.
If all is true for the match, add the data to the the 3rd sheet and continue.

Sub Newt()


 '' I will comment with 2  marks DThib
 
 'store the 3 worksheets in variables
 '' this all makes sense
    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    'variables to hold row numbers
    Dim MBRow As Long, CoRow As Long, QARow As Long
    'i & j store the last row number + 1 (next empty row) k is used as counter...
    Dim k As Long


    '' All the data is brought to this workbook in another macro. All data for this macro resides in worksheets here.
    'fully qualify each sheet bu providing workbook name and worksheet name
    Set MBs = ThisWorkbook.Sheets("MB51_Draw")
    Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
    Set QAws = ThisWorkbook.Sheets("QA_Data")
    
    '' Reading through your comments I agree that adding +1 to the row count below makes more sense.
    ''I have commented out the letters below.
    'get last used row in column A of MB51_Draw
    MBRow = MBs.Cells(Rows.Count, "A").End(xlUp).Row + 1
    'Get last row in COOIS_Draw
    CoRow = Coos.Cells(Rows.Count, "A").End(xlUp).Row + 1
    'Get Last row in QA_Data
    QARow = QAws.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'no need to do this, just add 1 to the previous 3 lines and use those variables.
   ' i = MBRow
    'j = QARow
   ' k = CoRow ' why set this when it is being used as a counter in the very next line?
    
    ''I removed the k conflict. The first if statement below is to match the id from Coos, the shorter list,
    ''to the MBs worksheet with the larger item list. If this value is found to match then the second "if" logic is to further refine the results.
    '' If the row/column in MBs is equal to the phrase [U]PTS2[/U]. If all these arguments are true, add the value from the Coos worksheet.
    '' to the QAws row and column and continue to add to that table with matches.
    '***k = 1 to CoRow and yet in the previous line k was set to equal CoRow...
   For k = 1 To CoRow
    If Coos.Cells(CoRow, 1).Value = MBs.Cells(MBRow, 13).Value Then
      If MBs.Cells(MBRow, 9).Value = "PTS2" Then
       QAws.Cells(QARow, 1).Value = Coos.Cells(CoRow, 4).Value
      End If
    End If
   Next
   
End Sub

DThib

 

[gallen]

Without me showing any understanding as to what you are trying to do so as you see any issue yourself:

Concentrating on this code:

    ''I removed the k conflict. The first if statement below is to match the id from Coos, _    the shorter list,to the MBs worksheet with the larger item list. _
    If this value is found to match then the second "if" logic is to further refine the results. _
    If the row/column in MBs is equal to the phrase PTS2. If all these arguments are true, _
    add the value from the Coos worksheet to the QAws row and column and continue _
    to add to that table with matches.
    
    '***k = 1 to CoRow and yet in the previous line k was set to equal CoRow...
   For k = 1 To CoRow
    If Coos.Cells(CoRow, 1).Value = MBs.Cells(MBRow, 13).Value Then
      If MBs.Cells(MBRow, 9).Value = "PTS2" Then
       QAws.Cells(QARow, 1).Value = Coos.Cells(CoRow, 4).Value
      End If
    End If
   Next

You have a loop using the variable 'k' to represent 1 to the last row in Sheets("COOIS_Draw"). And yet nowhere in the loop is 'k' referenced....