• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1668
  • Last Modified:

Excel week commencing formula

Is there a formula that when a name is entered into cell A2 the week commencing date automates in to cell B2? (Week commencing from a monday)
0
Carlandrewlewis
Asked:
Carlandrewlewis
  • 4
  • 2
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Something like the following snippet?

Chris
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range
 
    Set rng = Intersect(Target, Me.Range("a:A"))
    If rng Is Nothing Then Exit Sub
    
    For Each cel In rng
        If cel <> "" Then cel.Offset(0, 1) = DateAdd("d", -(Weekday(Date, vbTuesday)), Date)
    Next
    
End Sub

Open in new window

0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
To Create a macro:
------------------

Alt + F11 to open the macro editor
  For workbook event handlers:
     In the project tree select as appropriate:
      EXCEL      : thisworkbook
     In the workpane select as appropriate:
      EXCEL      : WorkBook
     In the workpane select the required 'event', (i.e. 'Change').
     Insert the required macro(s) into the selected subroutine.
Close the Visual Basic Editor.

Check Security as appropriate:
------------------------------

In the application select Tools | Macro | Security
Select Medium
Select OK

Chris
0
 
CarlandrewlewisAuthor Commented:
Is there a way to add this into this file. Basically when a date is entered into column A of 'New Seet' it should then automatically fill out column B with the week commencing that will then fill out column C with the week number. I'm just having trouble inserting the code in the right place....
Weekly-Pay-Schedule-1.xls
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
I have posted a change to your change event handler as follows in teh snippet below:

rCell.Offset(0, 1) = DateAdd("d", -(Weekday(rCell, vbTuesday)), rCell)

Chris
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
On Error GoTo e:
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rCell As Range
Set rCell = Intersect(Target, Columns("A"))
If rCell.Value = "" Then Exit Sub
rCell.Offset(0, 1) = DateAdd("d", -(Weekday(rCell, vbTuesday)), rCell)
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = rCell
ActiveSheet.Range("i3").Value = rCell
On Error GoTo e:
Application.ScreenUpdating = True
Exit Sub
e:
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
CarlandrewlewisAuthor Commented:
Works perfectly, thanks chris!!
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
You're welcome and thanks for the grade.

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now