IF statement works wrongly in excel vba-CodePudding

Here is the vba script, picture below is my expected result.

But the code wrongly displays as below.

Thus, I replace "else" with another if & "for each" statement, then it works perfectly.

However, I prefer modifying "else" to make it correct. Are there any problems in my IF...Else statement?

thanks for help!

Sub VLOOKUP1()

    Dim WS As Worksheet
    Dim LastRow As Integer, x As Integer, j As Integer
    Set WS = Workbooks("Book2").Worksheets(2)
    LastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).ROW
    
    For x = 2 To LastRow
        For j = 2 To 6

            If WS.Cells(j, 8).Value = WS.Cells(x, 1) Then
    
                WS.Cells(j, 8).Offset(0, 1).Value = WS.Cells(x, 2).Value
                WS.Cells(j, 8).Offset(0, 2).Value = WS.Cells(x, 3).Value
                WS.Cells(j, 8).Offset(0, 3).Value = WS.Cells(x, 5).Value
                WS.Cells(j, 8).Offset(0, 4).Value = WS.Cells(x, 6).Value

            Else
                WS.Cells(j, 8).Offset(0, 1).Value = "NO DATA"
                WS.Cells(j, 8).Offset(0, 2).Value = "NO DATA"
                WS.Cells(j, 8).Offset(0, 3).Value = "NO DATA"
                WS.Cells(j, 8).Offset(0, 4).Value = "NO DATA"

            End If
        Next j
    Next x

End Sub

Dim Table As Range, CELL As Range
Set Table = Range("H1:L6")

For Each CELL In Table
    If CELL.Value = "" Then
        CELL.Value = "N/A"
        CELL.Font.Color = vbRed
    End If
Next CELL

CodePudding user response：

Original answer was incorrect - see the edit at the end

It looks like you're trying to copy the value from row x to row j when the customer IDs match. However, every time you change row x, you're then comparing the new row x value against every row j value.

Here is a simple example. On the first loop iteration when x = 2 and j = 2, if customer ID 18226 was in cell (2, 1) and also in cell (2, 8), then the code would copy the details for 18226 into row 2. The inner loop would then go on to compare cell (2, 1) to cells (3, 8), (4, 8), (5, 8) and (6, 8). Assuming that each ID only appears once, then all those rows (3; 4; 5; 6) would have "NO DATA" copied into the cells.

But on the next iteration of the outer loop (so x = 3 and the inner loop starts with j = 2), if customer ID 12476 was in cell (3, 1) then that wouldn't match the customer ID in cell (2, 8) because we know that is 18226. The code would then overwrite the cells in row 2 with "NO DATA" and effectively undo what you did in the first iteration of the loop.

Assuming that you only want to copy data to each row j once - either to show the matching ID details or to show NO DATA - then you just need to swap the order of the loops:

For j = 2 To 6
    For x = 2 To LastRow
        ' do stuff
    Next x
Next j

edit: the original answer is still wrong. You also need to break out of the inner For loop with an Exit For as soon as you have found your match. Otherwise, you will end up overwriting the row with "NO DATA" on subsequent iterations of the inner loop:

For j = 2 To 6
    For x = 2 To LastRow
        If WS.Cells(j, 8).Value = WS.Cells(x, 1) Then
            WS.Cells(j, 8).Offset(0, 1).Value = WS.Cells(x, 2).Value
            WS.Cells(j, 8).Offset(0, 2).Value = WS.Cells(x, 3).Value
            WS.Cells(j, 8).Offset(0, 3).Value = WS.Cells(x, 5).Value
            WS.Cells(j, 8).Offset(0, 4).Value = WS.Cells(x, 6).Value
            Exit For
        Else
            ' do other stuff
        End If
    Next x
Next j