WeekNum, Month and Year

[Agatha1226]

Hi,

I have the following code and would like to get the week number based on the date on column E. I tried to run the code but nothing happens. Basically, it checks each cell in column S if it is blank. If the cell is blank, it should get the corresponding week number based on the date in column E. Can anyone check and correct?

Sub CleanUp()


Dim lRow As Long
'Dim lCol As Long
Dim i As Long


lRow = Cells(Rows.Count, 1).End(xlUp).Row
'lCol = Cells(1, Columns.Count).End(xlToLeft).Column


For i = 4 To lRow
  If Cells(i, 19) <> "" Then
    Cells(i, 19).Value = Application.WorksheetFunction.WeekNum(Cells(i, 5))
  Else
      Exit For
  End If
Next i


End Sub

 

[Agatha1226]

hi @gallen...please refer below for the edited code

Code:
Sub CleanUp()


Dim lRow As Long
'Dim lCol As Long
Dim i As Long


lRow = Cells(Rows.Count, 1).End(xlUp).Row
'lCol = Cells(1, Columns.Count).End(xlToLeft).Column


For i = 4 To lRow
If Cells(i, 19) = "" Then
Cells(i, 19).Value = Application.WorksheetFunction.WeekNum(Cells(i, 5))
'Else
'Exit For
End If
Next i


For i = 4 To lRow
If Cells(i, 20) = "" Then
Cells(i, 20).Value = Application.WorksheetFunction.Month(Cells(i, 5))
'Else
'Exit For
End If
Next i


For i = 4 To lRow
If Cells(i, 21) = "" Then
Cells(i, 21).Value = Application.WorksheetFunction.Year(Cells(i, 5))
'Else
'Exit For
End If
Next i


End Sub
[/Code]

 

[gallen]

I've simplified the code and tested. It works fine

Please see below:

Sub CleanUp()

Dim lRow As Long
Dim i As Long, x As Long


    'This is the only ;line it can go astray as it checks for last used row in column A. Is this correct?
[COLOR=#ff0000][B]    lRow = Cells(Rows.Count, 1).End(xlUp).Row[/B][/COLOR]


    'Loop through every row from row 4 to last used row
    For i = 4 To lRow
        'Loop  through columns S, T & U
        For x = 19 To 21
            'if the cell is blank
            If Cells(i, x) = "" Then
                'get the weeknum from column E of the row number 'i'
                Cells(i, x).Value = Application.WorksheetFunction.WeekNum(Cells(i, 5))
            End If
        Next x
    Next i
    
End Sub

The only place I can see that would cause issues is the line in red.

​That line checks for last used cell in column A. Is that correct?

 

[Agatha1226]

hi @gallen, yes that's correct...i tried your code and it worked perfectly. But the problem now is that it returns the week number for columns 20 and 21. It should return the Month for column 20 and the year for column 21 sorry im still really new at coding vba...

 

[Agatha1226]

hi @gallen...thanks so much for your help on this. I was able to make the code work by removing Application.WorksheetFunction to get the month and year values...thanks again!

 

[gallen]

No problem. You idnd't state what format you wanted the month and year so change the parts in red accordingly:

Sub CleanUp()

Dim lRow As Long
Dim i As Long, x As Long

    'This is the only ;line it can go astray as it checks for last used row in column A. Is this correct?
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Loop through every row from row 4 to last used row
    For i = 4 To lRow
        'Loop  through columns S, T & U
        For x = 19 To 21
            If Cells(i, x) = "" Then 'if the cell is blank
                Select Case x
                    Case 19 'WeekNum
                        Cells(i, x).Value = Application.WorksheetFunction.WeekNum(Cells(i, 5))
                    Case 20 'Month
                        Cells(i, x).Value = Format(Cells(i, 5), "[COLOR=#ff0000][B]mm[/B][/COLOR]")
                    Case 21 'Year
                        Cells(i, x).Value = Format(Cells(i, 5), "[COLOR=#ff0000][B]yyyy[/B][/COLOR]")
                End Select
            End If
        Next x
    Next i
    
End Sub

 

[gallen]

hi @gallen...thanks so much for your help on this. I was able to make the code work by removing Application.WorksheetFunction to get the month and year values...thanks again!

Ahh Just answered, but glad you've got it working