VBA Excel Compare sheets with nested For

Question

I want to compare every row of NBG_Data sheet with every row of Comparison_Data sheet , so i want each row of NBG_Data to be compared with all the rows of the Comparison_Data Sheet then go to the next row , till it reaches the MAX_Row ,the issue is that i couldn't Nest the For loop (which i really wanted) so i am trying the Do loop , but when i start the error is "Loop without Do" If anyone please tell me what I did wrong in the Do loop or better modify the for so i can have a nested working for loop .

For Row = 2 To MAX_Row


CompMonth = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, SOP).Value
CompMonth = DatePart("m", CompMonth)

CompYear = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, SOP).Value
CompYear = DatePart("yyyy", CompYear)


CompCarmaker = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, Carmaker).Value
CompProject = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, Project).Value
CompFamily = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, Family).Value
CompStatus = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, Status).Value
CompShare = Worksheets(NBG_ComparisonDataWorksheetName).Cells(Row, Share).Value


Do While Row <= 2

NBGMonth = Worksheets(NBG_DataWorksheetName).Cells(Row, SOP).Value
NBGMonth = DatePart("m", NBGMonth)

NBGYear = Worksheets(NBG_DataWorksheetName).Cells(Row, SOP).Value
NBGYear = DatePart("yyyy", NBGYear)

NBGCarmaker = Worksheets(NBG_DataWorksheetName).Cells(Row, Carmaker).Value
NBGProject = Worksheets(NBG_DataWorksheetName).Cells(Row, Project).Value
NBGFamily = Worksheets(NBG_DataWorksheetName).Cells(Row, Family).Value
NBGStatus = Worksheets(NBG_DataWorksheetName).Cells(Row, Status).Value
NBGShare = Worksheets(NBG_DataWorksheetName).Cells(Row, Share).Value

Row = Row + 1


 ' StatusBar Show

 Application.StatusBar = "VerifySumofShares. Progress: " & Row & " of " & MAX_Row


        If (NBGMonth = CompMonth And NBGYear = CompYear And CompCarmaker = NBGCarmaker And CompProject = NBGProject And CompFamily = NBGFamily And NBGStatus <> "LOST" And CompStatus <> "LOST" And CompShare + NBGShare <= 99 And CompShare + NBGShare > 100) Then



            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "A").Value = Row
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "B").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row, Project).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "C").Value = GetMonthAndQuarter(Worksheets(NBG_RegionaDataWorksheetName).Cells(Row, SOP).Value)
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "D").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row, Family).Value
            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "E").Value = Worksheets(NBG_RegionaDataWorksheetName).Cells(Row, Responsible).Value

           ' Region As String
            Region = ""

            'Add any other GeoRegion which is also responsible in the recorded data


            If Worksheets(NBG_DataWorksheetName).Cells(Row, "BC") Then
            Region = Region + "@EMEA"
            End If

            If Worksheets(NBG_DataWorksheetName).Cells(Row, "BD") Then
            Region = Region + "@AMERICAS"
            End If

            If Worksheets(NBG_DataWorksheetName).Cells(Row, "BE") Then
            Region = Region + "@GCSA"
            End If

            If Worksheets(NBG_DataWorksheetName).Cells(Row, "BF") Then
            Region = Region + "@JAPAN&KOREA"
            End If


            Worksheets(Issue_SumofSharesWorksheetName).Cells(3 + Issue_SumofSharesCnt, "F").Value = Region

            'Count the number of the cases recorded


            Issue_SumofSharesCnt = Issue_SumofSharesCnt + 1

            'If there is no items , the Message to show


        ElseIf (Worksheets(NBG_RegionaDataWorksheetName).Cells(Row, SOP).Value = "There are no items to show in this view.") Then


    End If

     Loop Until Row = MAX_Row

Next Row

Mathieu Guindon · Accepted Answer · 2016-12-06 15:42:04Z

1

You have a hybrid between a Do While...Loop and a Do...Loop Until loop, which VBA doesn't know how to interpret.

Either change:

    Do While Row <= 2

to:

Do

to make a valid Do...Loop Until loop.

Or change:

    Loop Until Row = MAX_Row

to:

    Loop

to make a valid Do While...Loop loop.

edited Dec 6, 2016 at 15:42

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

answered Dec 6, 2016 at 15:15

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nasser Al Fanek Over a year ago

Thank you very much

Nasser Al Fanek Over a year ago

the code worked perfectly but athere is a problem , now the "Next Row" (For loop) does not work , if you please can help me .

user3598756 Over a year ago

You can't update the current outer For- Next loop iterator (Row) inside the loop itself (you have that Row = Row + 1 inside the inner Do While Row <= 2 loop). You might want to turn the outer For Row = 2 To MAX_Row - Next Row loop into a Do - While Row <= MAX_Row one

Nasser Al Fanek Over a year ago

Okay , Thank you :)

Collectives™ on Stack Overflow

VBA Excel Compare sheets with nested For

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related