Solved

Excel week commencing formula

Posted on 2008-10-21
6
1,562 Views
Last Modified: 2011-10-19
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
Comment
Question by:Carlandrewlewis
  • 4
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
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
 

Author Comment

by:Carlandrewlewis
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:Carlandrewlewis
Comment Utility
Works perfectly, thanks chris!!
0
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
You're welcome and thanks for the grade.

Chris
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now