WeekNum, Month and Year

Agatha1226

New Member
Joined
Oct 18, 2016
Messages
10
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?

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


End Sub
 
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]
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
I've simplified the code and tested. It works fine

Please see below:

Code:
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?
 
Upvote 0
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...
 
Upvote 0
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!
 
Upvote 0
No problem. You idnd't state what format you wanted the month and year so change the parts in red accordingly:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top