hi everyone
i hit a big wall today i want to look up value if not exist in the column then copy value form another workbook.
this is my code :
this code work but when i run twice, its still copy the value even "Good" is exists in column and multiple the value from up above (
i hit a big wall today i want to look up value if not exist in the column then copy value form another workbook.
this is my code :
Rich (BB code):
Private Sub kas_awal()
Application.ScreenUpdating = False
Dim c1 As Range, lastrw As Long
Dim basepath, N_kas As String
Dim ws, kas As Worksheet
Dim dt As Date
basepath = "D:\Documents\LAP\"
basepath = basepath & Year(Date) & "\lookup.xlsm"
Set ws = ThisWorkbook.Worksheets("Admin")
Set kas = Workbooks.Open(basepath).Worksheets("Admin")
N_kas = "Good"
With CreateObject("scripting.dictionary")
For Each c1 In ws.Range("C1", ws.Range("C" & Rows.Count).End(xlUp))
If Not .Exists(N_kas) Then 'if my sheet doesnt contain "Good" in column C then
nval = kas.Range("I33").Value ' i want to copy this cell value
dt = Format(Date, "dd/mm/yyyy")
With ws
lastrw = .Cells(.Rows.Count, 1).End(xlUp).Row
.Unprotect ("xxx")
.Cells(lastrw + 1, 1).Value = dt
.Cells(lastrw + 1, 2).Value = "UMT"
.Cells(lastrw + 1, 3).Value = "Good"
.Cells(lastrw + 1, 4).Value = nval
End With
End If
Next c1
End With
ws.Activate
End Sub