Sub sp_CariData()
Dim lRow As Long
Dim lFind As Long
Dim sSh2 As String, sSh3 As String, sKey As String
sSh2 = "Sheet2"
sSh3 = "Sheet3"
lRow = Range("A" & Rows.Count).End(xlUp).Row
If lRow < 4 Then Exit Sub
On Error Resume Next
For i = 4 To lRow
sKey = Range("A" & i).Value
lFind = 0
With Sheets(sSh2)
lFind = .Cells.Find(sKey).Row
If lFind > 0 Then
Range("C" & i).Value = .Range("A" & lFind).Value
Range("D" & i).Value = .Range("B" & lFind).Value
Range("E" & i).Value = .Range("C" & lFind).Value
Range("F" & i).Value = .Range("L" & lFind).Value
Range("G" & i).Value = .Range("P" & lFind).Value
'tambahan
range("G" & i).value = iif( Range("F" & i).Value = "JECT" , "" , range("G" & i).value )
End If
End With
If lFind = 0 Then
With Sheets(sSh3)
lFind = .Cells.Find(sKey).Row
If lFind > 0 Then
Range("C" & i).Value = .Range("A" & lFind).Value
Range("D" & i).Value = .Range("B" & lFind).Value
Range("E" & i).Value = .Range("C" & lFind).Value
Range("F" & i).Value = .Range("L" & lFind).Value
Range("G" & i).Value = .Range("P" & lFind).Value
'tambahan
range("G" & i).value = iif( Range("F" & i).Value = "JECT" , "" , range("G" & i).value )
End If
End With
End If
If lFind = 0 Then Range("C" & i).Value = "Not Found"
Next i
MsgBox "Proses pencarian selesai", vbInformation, "Info"
End Sub
baik.silahkan dicoba ini, sy blm tes tapi harusnya berhasil (ada penambahan )