I have a macro that is supposed to search for a range of values in workbooks range k4:k43 and when it finds those in workbook1 column B, it puts a timestamp in column D of the same row. With this code I am getting a Compile Error Next Without For error. Can anyone help me out on this please?
Macro to search column for matching value and add timestamp
- Sam261
- Thread is marked as Resolved.
-
-
-
Unable to open document.
-
Hello,
In an Excel Forum ... it would seem quite obvious ... to attach ... well ... an Excel file ...
-
Code
Display MoreApplication.ScreenUpdating = False Dim j As Long, i As Long Dim lastRow1 As Range, lastRow2 As Range Dim sh_1, sh_2 As Worksheet Dim barcode As String Dim wrkbk1 As Workbook, wrkbk2 As Workbook Set wrkbk2 = ThisWorkbook Set wrkbk1 = Workbooks.Open("H:\users\Scoti\Timer.xlsx") Set sh_1 = wrkbk2.Sheets("Nucomat_Dashboard") Set sh_2 = wrkbk1.Sheets("Times") lastRow1 = sht_1.Range("K4:K43").End(xlDown).Rows.Count barcode = sh_1.Cells(j, 1).Value For j = 2 To lastRow1 With wrkbk1 lastRow2 = sh_2.UsedRange.Rows.Count For i = 2 To lastRow2 If sh_2.Cells(i, 2).Value = barcode Then sh_2.Cells(i, 4).Value = Format$(Now(), "yyyy-mm-dd hh:nn:ss") End If Next i Next j ActiveWorkbook.Save ActiveWindow.Close Application.ScreenUpdating = True End With End Sub
-
I am unable to attach the entire workbooks due to their size.
-
As a first step ...line #18 ... replace nn by mm ...
sh_2.Cells(i, 4).Value = Format$(Now(), "yyyy-mm-dd hh:mm:ss")
-
I am using the "nn" for minutes in another macro that is working well. What is the difference between the two?
-
nn 00-59 (Minute of hour, with a leading zero) mm 00-59 (Minute of hour, with a leading zero). Only if preceded by h or hh To answer your question ...
-
I will have to test that on my other macro and see. It seems to be working fine. Thanks for the info.
-
Thanks for your Thanks
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!