WeekNum function - unable to insert into vba code

[lYoung]

my code reads

dim rowCount As Integer

rowCount = Range("J1").CurrentRegion.Rows.Count


For i = 1 To rowCount

Range("J:" & i) = Application.WeekNum(Range("K:" & i))



Next i


Note - Column K are date formatted and column J is number formatted.

Error message "Run-time error '1004'
Method 'Range' of object'_Global' failed

 

[MARK858]

Range("J:" & i) and Range("K:" & i) should be Range("J" & i) and Range("K" & i).
You also haven't dimmed your i variable.

 

[jasonb75]

Depending on the required method of week numbering, perhaps

Dim c As Range, lRow As Long
lRow = Cells(Rows.Count, 11).End(xlUp).Row
For Each c In Range("J2:J" & lRow)
    c.Value = Format(c.Offset(, 1), "ww")
Next

 

[lYoung]

Depending on the required method of week numbering, perhaps

Dim c As Range, lRow As Long
lRow = Cells(Rows.Count, 11).End(xlUp).Row
For Each c In Range("J2:J" & lRow)
    c.Value = Format(c.Offset(, 1), "ww")
Next

awesome - this worked. What function/purpose does the "ww" serve? Is it simply pulling out the # for the week date provided? So this was a work around to using weeknum? thank you!

 

[MARK858]

So this was a work around to using weeknum? thank you!

See post number 2, weeknum was fine. You were just using the wrong syntax for the ranges.

rowCount = Range("J1").CurrentRegion.Rows.Count

is also not the best way to determine the last row especially as you are inputting the weeknum from J1 which is a bit odd as your currentregion starts there.

 

[jasonb75]

It was an alternative, not a workaround. I personally try to avoid using worksheet functions in vba when and where possible. The vba format function has a couple of additional options that worksheet formats don't have, such as "w" for week number or "q" for quarter. There may be others but those are the only ones that I can think of.

I was originally just going to correct the typos in your original code but @MARK858 beat me to it in post 2.