Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel week commencing formula

Posted on 2008-10-21
6
Medium Priority
?
1,641 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
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

971 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