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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
This line:
Code:
[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
Code:
[COLOR=#333333] If Cells(i, 19) [/COLOR][B][COLOR=#ff0000]= [/COLOR][/B][COLOR=#333333]""[/COLOR]
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
633
Office Version
365
Platform
Windows
Just comment the "Else Exit For" part because if the first cell doesn't meet the specified criteria it will exit the loop
 

Agatha1226

New Member
Joined
Oct 18, 2016
Messages
10
hi @gallen...yes...thank you both! I appreciate you taking time to help me with this!
 

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
633
Office Version
365
Platform
Windows
Glad I could help :)

You are welcome
 

Agatha1226

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

Well-known Member
Joined
Jun 27, 2011
Messages
1,996
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,113
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top