@ddtopgun
...akan tetapi jika tanpa ada kolom tambahan, maka store delivery itu merupakan jumlah dari beberapa kolom dimulai dari kolom U - AA
gmn utk menjumlahkan beberapa kolom tersebut
ya di SUM donk :)
Option Explicit
Sub TesAmbilData()
Dim file1 As Workbook
Dim file2 As Workbook
Dim ket As Range, rTarget As Range, yTarget As Range
Dim sel As Range, xsel As Range, x As String
Dim r As Long, c As Long
Set file1 = ThisWorkbook
'note : sesuaikan sj cara ambil file2 nya
Set file2 = Workbooks.Open(ThisWorkbook.Path & "\file2.xlsx")
Set ket = file1.Sheets("Tes").Range("D2:D20")
Application.ScreenUpdating = False
On Error Resume Next
For Each sel In ket
'jika keterangan pendek
x = sel.Offset(0, -1)
If Len(sel) = 6 Then
With file2.Worksheets("Sheet1")
Set rTarget = .Range("B3:B" & .Range("B" & Rows.Count).End(xlUp).Row)
Set yTarget = .Range("H1:AA1")
'periksa baris kolom B pada file2
For Each xsel In rTarget
If xsel = sel Then
r = xsel.Row
'jika store delivery
If x = "Store Delivery" Then
sel.Offset(0, -2) = WorksheetFunction.Sum(.Range("U" & r & ":AA" & r))
GoTo berikut
Else
'cari kolom nama proses pada file2
c = .Cells.Find(x, LookAt:=xlWhole).Column
If Err = 0 Then sel.Offset(0, -2) = .Cells(r, c)
GoTo berikut
End If
End If
Next
End With
Else
'jika keterangan panjang
With file2.Worksheets("Sheet1")
Set rTarget = .Range("A3:A" & .Range("B" & Rows.Count).End(xlUp).Row)
Set yTarget = .Range("H1:AA1")
'periksa baris kolom A pada file2
For Each xsel In rTarget
If xsel = sel Then
r = xsel.Row
'jika store delivery
If x = "Store Delivery" Then
sel.Offset(0, -2) = WorksheetFunction.Sum(.Range("U" & r & ":AA" & r))
GoTo berikut
Else
'cari kolom nama proses pada file2
c = .Cells.Find(x, LookIn:=xlValues, LookAt:=xlWhole).Column
If Err = 0 Then sel.Offset(0, -2) = .Cells(r, c)
GoTo berikut
End If
End If
Next
End With
End If
berikut:
Next
Err.Clear: file2.Close: Application.ScreenUpdating = True
End Sub