Complex loop is not working for 3 worksheets

DThib · Jul 8, 2019

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

Code:

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

DThib · Jul 10, 2019

OK.
1) this macro is taking data copied to 2 worksheets (MB51_Draw, COOIS_Draw) in the workbook (NH1)
2) The data is from two sources that have the same item IDs going through a process
(MB51_Draw) and then returning (COOIS_Draw) from this and creating a record.
3) In the 2 worksheets the common factor is a column holding the item ID.
4) If the process is complete, the ID will appear in (COOIS_Draw). This is the match I am trying to draw
on to place information in a 3rd worksheet (QA_Data) in the same workbook (NH1).
5) The first IF statement is looking for a match in the dynamic range of the item ID on worksheets (MB51_Draw, COOIS_Draw)
6) The first wrinkle in my code is there are typically 2 to 3 lines with the matching Item ID in (MB51_Draw). To further define a choice, I am trying the second IF statement to say now that you have found the matching ID find a word that appears in a column in one of those lines.
7) If you find these matches,then take from the rows in both worksheets certain data and place in the third worksheet(QA_Data) in a table.
8)The loop is to look through all column 1(COOIS_Draw) Item IDs for matches in (MB51_Draw).
My latest attempt is below:

Code:

Sub Newt_II()


    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    Dim MBRow As Long, CoRow As Long, QARow As Long
    Dim m As Long


    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 + 1
    '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 + 1
    
    
   For m = 1 To CoRow
    If MBs.Cells(MBRow, 13).Value = Coos.Cells(m, 1).Value Then
      If MBs.Cells(MBRow, 9).Value = "PTS2" Then
        QAws.Cells(QARow, 1) = Coos.Cells(m, 4).Value
        QAws.Cells(QARow, 2) = Coos.Cells(m, 5).Value
        QAws.Cells(QARow, 3) = MBs.Cells(MBRow, 13).Value
        QAws.Cells(QARow, 4) = MBs.Cells(MBRow, 17).Value
        QAws.Cells(QARow, 5) = MBs.Cells(MBRow, 14).Value
      End If
    End If
   Next
   
End Sub

DThib

DThib · Jul 10, 2019

Update!
I now have it pulling correctly! Yeah!!!!

It is only giving me the last match when there are 12 defined that should be added before the last match.
What is missing?
Please help!

Code:

Sub Newt()


    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    Dim MBRow As Long, CoRow As Long, QARow As Long
    Dim m As Long, j As Long, g As Long


    Set MBs = ThisWorkbook.Sheets("MB51_Draw")
    Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
    Set QAws = ThisWorkbook.Sheets("QA_Data")
    
    MBRow = MBs.Cells(Rows.Count, "A").End(xlUp).Row
    CoRow = Coos.Cells(Rows.Count, "A").End(xlUp).Row
    QARow = QAws.Cells(Rows.Count, "A").End(xlUp).Row
    
     For m = 1 To CoRow
        If Coos.Cells(m, 1).Value = MBs.Cells(MBRow, 13) Then
          If MBs.Cells(MBRow, 9).Value = "PTS2" Then
            QAws.Cells(QARow, 1) = Coos.Cells(m, 4).Value
            QAws.Cells(QARow, 2) = Coos.Cells(m, 5).Value
            QAws.Cells(QARow, 3) = MBs.Cells(MBRow, 13).Value
            QAws.Cells(QARow, 4) = MBs.Cells(MBRow, 17).Value
            QAws.Cells(QARow, 5) = MBs.Cells(MBRow, 14).Value
          End If
        End If
     Next
   
End Sub

DThib

gallen · Jul 11, 2019

Above each line of code, I've added a comment in plain Eng, sorry, American, describing what each line does.

Read them carefully and see if they are doing exactly as you want.

Code:

Sub Newt()

    Dim MBs As Worksheet, Coos As Worksheet, QAws As Worksheet
    Dim MBRow As Long, CoRow As Long, QARow As Long
    Dim m As Long ', j As Long, g As Long j and g not used


    Set MBs = ThisWorkbook.Sheets("MB51_Draw")
    Set Coos = ThisWorkbook.Sheets("COOIS_Draw")
    Set QAws = ThisWorkbook.Sheets("QA_Data")
    
    MBRow = MBs.Cells(Rows.Count, "A").End(xlUp).Row
    CoRow = Coos.Cells(Rows.Count, "A").End(xlUp).Row
    QARow = QAws.Cells(Rows.Count, "A").End(xlUp).Row
    
    
    'Loop from 1 to last row in 'COOIS_Draw' sheet
     For m = 1 To CoRow
     
        'if the value in row 'm' of column 'A' equals the value _
        in the last row of column "M" on sheet 'MB51_Draw' then...
        If Coos.Cells(m, 1).Value = MBs.Cells(MBRow, 13) Then
        
            'If the value in the last row of column I on sheets MB51_Draw _
            equals "PTS2" then...
            If MBs.Cells(MBRow, 9).Value = "PTS2" Then
            
                'Value of last row of column A on sheet 'QA_Data' equals _
                row 'm' of column D on sheet 'COOIS_Draw'
                QAws.Cells(QARow, 1) = Coos.Cells(m, 4).Value
                
                'Value of last row of column B on sheet 'QA_Data' equals _
                row 'm' of column E on sheet 'COOIS_Draw'
                QAws.Cells(QARow, 2) = Coos.Cells(m, 5).Value
                
                'Value of last row of column C on sheet 'QA_Data' equals _
                last row of column M on sheet 'MB51_Draw'
                QAws.Cells(QARow, 3) = MBs.Cells(MBRow, 13).Value
                
                'Value of last row of column D on sheet 'QA_Data' equals _
                last row of column Q on sheet 'MB51_Draw'
                QAws.Cells(QARow, 4) = MBs.Cells(MBRow, 17).Value
                
                'Value of last row of column E on sheet 'QA_Data' equals _
                last row of column N on sheet 'MB51_Draw'
                QAws.Cells(QARow, 5) = MBs.Cells(MBRow, 14).Value
            End If
        End If
    'add 1 to 'm' and loop until m = last used row in 'COOIS_Draw'
     Next m
   
End Sub

The best way of fixing these issues is to step through your code using F8 and checking that the result of each line does exactly as you expect.

If unsure just goggle 'stepping through code VBA'

DThib · Jul 11, 2019

Thanks again Gallen,

I will step through now. I do know how to d that but thank you for the suggestion.
I will let you know how it does.

DThib

DThib · Jul 11, 2019

I am trying to run through all entries in column (13) of MBs and find the match.
The entries from column (1) of Coos are a smaller number and each one needs to be compared to the entire column from MBs.

Code:

    'if the value in row 'm' of column 'A' equals the value _
        in the last row of column "M" on sheet 'MB51_Draw' then...
        If Coos.Cells(m, 1).Value = MBs.Cells(MBRow, 13) Then

This is not looping and only settling on the last line of matching information.

gallen · Jul 11, 2019

Forgive me for not writing the actual code, I'll be honest that I'm not 100% clear, but understand you are searching for a value in a list of values. (I normally use the 'Find' function)

As you can see from that line of code it will always look last row of column(13) as it never changes.

The value that changes in the loop (the counter) is variable 'm'.

It seems you may need a second loop(nested loop) to go from the first row of data to MBRow...

DThib · Jul 11, 2019

Success! Nested the Coos and MBs loops and now it is pulling.

Thanks for the patience and suggestions.

DThib

gallen · Jul 12, 2019

Great news! You understand so much more when you work out the solution yourself.

Complex loop is not working for 3 worksheets

DThib

Active Member

DThib

Active Member

Excel Facts

DThib

Active Member

gallen

Well-known Member

DThib

Active Member

DThib

Active Member

gallen

Well-known Member

DThib

Active Member

gallen

Well-known Member

Similar threads

Forum statistics

Share this page

Complex loop is not working for 3 worksheets

Active Member

Active Member

Excel Facts

Active Member

Well-known Member

Active Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock