0

please could someone help me. I have been trying and could not seems to resolve this.

I want to compare the date in Column J to the current week. My code also include finding the last row and automatically loop until the last row.

I currently trying with the following code but having issue with

Recievedate = Format(Cells(i, "J").Value, "ww-yyyy")

I have tried

Recievedate = Format(Range(i, "J").Value, "ww-yyyy")

which was found in another post but it doesn't work. Can someone tell me what have I done wrong please? Please ignore the code after MsgBox "OK" as I am trying to build this step by step. Thank you.

        Sub macro1()
    
    Range("A1").Select
    Range("A" & Rows.Count).End(xlUp).Select ' Find last row in column A and remember the active cell
       
    Dim i As Integer
    Dim Recievedate As Date
    
    For i = 2 To ActiveCell.Row 'start from row 2 and automatically +1 until it reach the active cell
    Recievedate = Format(Cells(i, "J").Value, "ww-yyyy")
    
        If Recievedate = Format(Date, "ww-yyyy") Then
        MsgBox "OK"
        
        End If 

Next i

...
xidgel
  • 3,085
  • 2
  • 13
  • 22
Crabscoot
  • 3
  • 1

3 Answers3

1

Try

Sub macro1()
    
    Range("A1").Select
    Range("A" & Rows.Count).End(xlUp).Select ' Find last row in column A and remember the active cell
       
    Dim i As Integer
    ' Dim Recievedate As Date
    Dim Recievedate As string
    
    For i = 2 To ActiveCell.Row 'start from row 2 and automatically +1 until it reach the active cell
    Recievedate = Format(Cells(i, "J").Value, "ww-yyyy")
    
        If Recievedate = Format(Date, "ww-yyyy") Then
        MsgBox "OK"
        
        End If 

    Next i

With this statement Recievedate = Format(Cells(i, "J").Value, "ww-yyyy") you assign a string to Recievedate which cannot work in case Recievedate is declared as date.

Another approach could be to use WorksheetFunction.WeekNum instead.

Storax
  • 11,158
  • 3
  • 16
  • 33
0

A possible solution - note, no Selecting required.

Sub Test()

    Dim lRow As Long
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Dim i As Long
    Dim ReceivedDate As Date
    
    For i = 2 To lRow
        ReceivedDate = Cells(i, "J")
        If Format(ReceivedDate, "ww-yyyy") = Format(Date, "ww-yyyy") Then
            MsgBox "Row " & i & " is ok.", vbOKOnly + vbInformation
        End If
    Next i
    
End Sub
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • This is interesting. Could you share what is the benefits of not using selecting please? – Crabscoot Mar 23 '21 at 12:37
  • If the user selects a different cell while your code is running, if the cell that is being selected is locked so the code can't select it... basically giving the user a chance to muck up your hard thought over code. There's an (almost) obligatory [answer](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) that deals with avoiding select. – Darren Bartrup-Cook Mar 23 '21 at 16:03
  • Thank you. I will improve my code with this suggestion. – Crabscoot Mar 29 '21 at 20:17
0

I suggest you look for a date that is between the start and end dates of the current week.

For example (assuming your week starts on Sunday and ends on the subsequent Saturday)

Sub CurrentWeek()
    Dim dt As Date
    Dim dtBOW As Date, dtEOW As Date
    Dim c As Range, r As Range
    
dtBOW = Date - Weekday(Date - 1) 'Beginning of week
dtEOW = Date + 7 - Weekday(Date) 'End of week

Set r = ThisWorkbook.Worksheets("Sheet2").Columns(10) 'Column J, fully qualified

For Each c In r.Cells
    If c >= dtBOW And c <= dtEOW Then
        MsgBox "found it at " & c.Address
        c.Select 'Selected only so as to highlight it on the worksheet
        Exit Sub
    End If
Next c

MsgBox "no luck"

End Sub
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60