Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel week commencing formula

Posted on 2008-10-21
6
Medium Priority
?
1,630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22765650
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
ID: 22765653
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
ID: 22765733
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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 22765831
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
ID: 31508189
Works perfectly, thanks chris!!
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 22766160
You're welcome and thanks for the grade.

Chris
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

722 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