How to use this loop ?

[vumian]

Dear all,

Please open my file, enable macro, then Ctrl D to run it

i wanna check what items in DRP sheet, that does not exist in LoadingPlan sheet, If it's not, then msgbox "shortage this item"

it's rough here, and there is some blank cells at columns E

Do While Sheet1.Cells(i, 5) <> what

How to loop to end of last row including blank cells inside range

Thanks in advance

[LucB]

Hi vumian,

Its not exactly you asked for, but have a look at column R.

[Dav]

The previous reply is potentially more useful, as the same value cam be missing more than once in the second sheet, and indeed is in your sheet

try this as the macro

Sub DRP()

' Keyboard Shortcut: Ctrl+d
'
Dim i As Integer
Sheets("DRP").Select
Range("E65536").Select
Selection.End(xlUp).Select
endrow = ActiveCell.Row
    i = 2
    For i = 2 To endrow
        temp = Sheets("DRP").Cells(i, 5).Value
        If WorksheetFunction.CountIf(Sheets("LoadingPlan").Range("b:B"), temp) = 0 And temp <> "" Then
       ' Cells(i, 18).Value = "Shortage of this item"
        MsgBox "Hix, Shortage this Item :  " & temp
        End If
    Next i
        
End Sub

and if you wish comment out the msgbox and include the line above it which is green

does that help

regards

Dav

[vumian]

Dear Dav,

Your code is good, If multiple Itemcode is the same at separate multiple rows, then i wanna msgbox once inteads of your currently code get many loop

Thanks in advance

[Dav]

thats why i suggested the option with the line above being used and the comment line being disabled. How about the following option

Sub DRP()

' Keyboard Shortcut: Ctrl+d
'
Dim i As Integer
Dim absent As String
Dim match1 As Integer
Dim match2 As Integer
Sheets("DRP").Select
Range("E65536").Select
Selection.End(xlUp).Select
endrow = ActiveCell.Row
absent = " "
    i = 2
    For i = 2 To endrow
        temp = Sheets("DRP").Cells(i, 5).Value
        match1 = InStr(1, absent, " " & temp, vbTextCompare)
        match2 = WorksheetFunction.CountIf(Sheets("LoadingPlan").Range("b:B"), temp)
        If match2 = 0 And temp <> "" And match1 = 0 Then
       ' Cells(i, 18).Value = "Shortage of this item"
        MsgBox "Hix, Shortage of item:  " & temp
        End If
        absent = absent & " " & temp
    Next i
        
End Sub

is that what you want

Regards

Dav

[vumian]

hi Dav,

Thank you a lot

all the best,
vumian