Option Explicit
Public Sub search()
ThisWorkbook.Sheets(1).Activate
Dim MaxRow1, MaxRow2 As Long
MaxRow1 = Sheets(1).Cells(1048576, 3).End(xlUp).Row
Dim i, j As Integer
For i = 2 To MaxRow1
MaxRow2 = Sheets(2).Cells(1048576, 2).End(xlUp).Row
' If MaxRow2 = 1 Then
' Sheets(2).Cells(2, 1).Value = UCase(Sheets(1).Cells(i, 1).Value)
' Sheets(2).Cells(2, 2).Value = UCase(Sheets(1).Cells(i, 3).Value)
' Sheets(2).Cells(2, 20).Value = Sheets(1).Cells(i, 14).Value
' Else
For j = 1 To MaxRow2
If Sheets(2).Cells(j, 2).Value = UCase(Sheets(1).Cells(i, 3).Value) Then
Sheets(2).Cells(j, 20).Value = Sheets(2).Cells(j, 20).Value + Sheets(1).Cells(i, 14).Value
' NGName = Split(Sheets(1).Cells(i, 19).Value, ",")
' NGQty = Split(Sheets(1).Cells(i, 18).Value, ",")
Exit For
Else
If j = MaxRow2 Then
Sheets(2).Cells(j + 1, 1).Value = UCase(Sheets(1).Cells(i, 1).Value)