We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Macro and/or function that determines what week a date falls into

John Carney
John Carney asked
on
Medium Priority
279 Views
Last Modified: 2012-05-11
Please take a look at the attached workbook. I need something that will tell me the week for all the dates in Column BA.

Thanks,
John
FindWeek.xls
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2008

Commented:
Place the formula in AY and copy down as far as needed. You don't need the table in the header - the only cell referenced is BB1 - the first day of the first week of the year.

Kevin
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thanks, Kevin, I think that will do it. In the meantime, I figured out a macro that will do it with the table. Now I have two ways to do it. I like yours because there's no need for the table! However you can still tell me how impressed you are that I was ab;e to figure out this macro :-)

Thanks,
John


Sub FindRow()
Dim cel As Range
For Each cel In Range("BA6:BA320")
cel.Select
Dim wk As Range, x As Long
ActiveWorkbook.Names.Add Name:="ThisWeek", RefersTo:=cel.EntireRow
x = [ThisWeek].Row
cel.Offset(0, -3) = x
Set wk = [ThisWeek].Find(What:="week", After:=Cells(x, 53), LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext)
cel.Offset(0, -1) = wk
Next cel
End Sub

Open in new window

CERTIFIED EXPERT
Top Expert 2008

Commented:
I'm not sure if I should be impressed or appalled! What the heck is that macro doing? Creating named ranges? Inside the loop? Dang!
John CarneyReliability Business Tools Analyst II

Author

Commented:
How about impressed AND appalled!

Thanks, Kevin :-)

- John
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.