Using vba to go to column with current week

[DracoDormiens]

Hi there,Sorry if I am posting a new thread and it has already been answered else where. I have looked and looked, but no luck so far.I have a time & resource planning tool which has fixed data on columns A to G (name , activity , priority etc). From H on wards are week to week columns.What I am trying to do is get the current week to pop up first and scroll all the way to the left. This means that past planning information is not stuck between the fixed columns (A to G) and the current and future relevant weeks. So I thought I managed to crack this by using the following VBA code:Sub gotoDate ()Dim c As RangeDim d As Dated = Date For Each c In Range("H4:AT4") If c = d Then Application.Goto c, True End If Next cEnd SubThis worked/ works beautifully if we are on the actual date today that is shown on the sheet. So for example, my week to week inputs are 19-Aug-19, 26-Aug-19, 02-Sep-19 and so on. The sheet will only go to the 19-Aug-19 week column if it is the 19-Aug-19. What I am looking to do is essentially what I have, but add a further range for that week instead. Can anyone help me out? Thanks,D

 

[KOKOSEK]

Please use CODE tag around vba code. Now it's hard to read really.

 

[Joe4]

Welcome to the Board!

As Koksek mentioned, your code is basically unreadable, as it is appearing as one long string.
Here are instructions on how to post your code so it appears in a readable format: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

 

[Fluff]

Hi & welcome to MrExcel.
How about

d = Date - Weekday(Date, vbMonday) + 1

 

[DracoDormiens]

Hi there, First Noob move on my first post. hope this works.

Sorry if I am posting a new thread and it has already been answered else where. I have looked and looked, but no luck so far.I have a time & resource planning tool which has fixed data on columns A to G (name , activity , priority etc). From H on wards are week to week columns.What I am trying to do is get the current week to pop up first and scroll all the way to the left.
This means that past planning information is not stuck between the fixed columns (A to G) and the current and future relevant weeks. So I thought I managed to crack this by using the following VBA

Sub gotoDate ()
Dim c As Range
Dim d As  Date
d = Date
    For Each c In Range("H4:AT4")        
If c = d Then             
Application.Goto c, True        
End If    
Next c
End Sub

This worked/ works beautifully if we are on the actual date today that is shown on the sheet. So for example, my week to week inputs are 19-Aug-19, 26-Aug-19, 02-Sep-19 and so on. The sheet will only go to the 19-Aug-19 week column if it is the 19-Aug-19. What I am looking to do is essentially what I have, but add a further range for that week instead. Can anyone help me out? Thanks,D

 

[DracoDormiens]

Amazing Fluff, this worked !! don't know how but amazing ! thank you !!

 

[Fluff]

You're welcome & thanks for the feedback.
This part

Weekday(Date, vbMonday)

returns the day of the week starting with Monday as 1, so for today it returns 3
That is then subtracted from today's date to give 18/8/19 & then we add 1 to bring it back to Mondays date, the 19th