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

 

[gallen]

This line:

[COLOR=#333333] If Cells(i, 19) <> "" Then
[/COLOR]    [COLOR=#333333]Cells(i, 19).Value = Application.WorksheetFunction.WeekNum(Cells(i, 5))[/COLOR]

means only get the weeknum if column "S" HAS a value which is the opposite of what you stated. Going off your description it should read

[COLOR=#333333] If Cells(i, 19) [/COLOR][B][COLOR=#ff0000]= [/COLOR][/B][COLOR=#333333]""[/COLOR]

 

[Agatha1226]

Thanks @gallen..I updated the code as advised but nothing is happening...the cells in column S are still blank

 

[mse330]

Just comment the "Else Exit For" part because if the first cell doesn't meet the specified criteria it will exit the loop

 

[Agatha1226]

Thank you @mse33!

 

[gallen]

Just comment the "Else Exit For" part because if the first cell doesn't meet the specified criteria it will exit the loop

Of course. Shudda spotted that.

Does that fix your problem now @Agatha1226?

 

[Agatha1226]

hi @gallen...yes...thank you both! I appreciate you taking time to help me with this!

 

[mse330]

Glad I could help

You are welcome

 

[Agatha1226]

Hi @gallen....would it be possible to extend this so that it will also check columns T and U and update cells under these 2 columns with the month and year values from the date entered under column E? I tried replicating the For loop by changing the corresponding column numbers, (20 and 21 in place of 19) but it gives me "Run time error 438, Objecy doesn't support this property or method"

 

[gallen]

Can you post the full code you have now that works as you want for column S?

Changing the column numbers should be fine so something else is stopping it.