To get date of 3rd monday of each month

Question

0.00/5 (No votes)

See more:

Hi,

I want to create a macro that will add one column in my excel. It will check if the date is 3rd monday of the current month. Depending on the condition it will add a column in my excel at the 3rd last position.

The excel will contain 6months data only.
So every month a column is deleted and one column is added in the excel.
Please let me know how this can be done using macro as i am not able to get date for 3rd monday of each month.

Also, it is possible to defined multiple macros on a single button?

Appreciate all the help.

Thanks in advance.

Posted 8-Aug-12 23:33pm

archies_gall

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ChandraRam · Answer 1 · 2012-08-11T07:08:00

Solution 2

Find out the first Monday - start with day 1 of the month and check the week day in a loop until you hit Monday. Add 14 to this date to get the 3rd Monday.

HTH

Posted 11-Aug-12 7:08am

ChandraRam

Kenneth Haugland · Answer 2 · 2012-08-11T08:07:00

Solution 3

For adding one column at the beginning of the sheet you simply typesomething like the code below:

ActiveSheet.Columns(1).EntireColumn.Insert

And after you have done that you simply type:

ActiveSheet.Columns(ActiveSheet.UsedRange.Columns.Count).EntireColumn.Delete

to delet the last used column.

Here is how you get the day of week:
http://msdn.microsoft.com/en-us/library/system.datetime.dayofweek.aspx[^]

It should be easy for you to loop through the first date in a month, and simply count three Mondays.

This question here I dont actually get:

Quote:
Also, it is possible to defined multiple macros on a single button?

You can write as much code as you like in a single macro, you could also have several macros in one excel file, so I dont understand what you mean here...

I hope this helps :)

Posted 11-Aug-12 8:07am

Kenneth Haugland

Comments

archies_gall 12-Aug-12 0:46am

Hi Kenneth,

Thanks for your reply.

I am not sure of macros as i am using them for the first time.

Suppose i have one button in my excel sheet..
On VBA editor i have two macros defined
Eg:

Sub OpenProcessCloseFile()
Dim MonthlyWB As Variant
Dim FileName As String

MonthlyWB = Application.GetOpenFilename( _
FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Open Workbook")

Workbooks.Open MonthlyWB
FileName = ActiveWorkbook.Name

' INSERT YOUR ADDITIONAL CODE HERE
' Note: to run a macro in the other file, use code like this
' Application.Run ("'" + FileName + "'!MyMacro")
' -- or Applicion.Run ("'My Workbook.xls'!MyMacro")

Workbooks(FileName).Close
End Sub
--------------------------------------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save
End Sub

The button which i added in my excel when i try to assign a macro it gives me two options to select: "OpenProcessCloseFile" and "Workbook_BeforeClose"
but i want to use only one button in my excel sheet which will perform both actions on a single click.

Hope this clarifies.
if not I will try to explain it in more clear way.

Thanks once again for your help.

Thanks!
Archie

ChandraRam 13-Aug-12 1:37am

If you want to call both functions, one after the other, from your button click, write another function that has just the two lines - one call to each of the functions.
HTH

archies_gall 15-Aug-12 5:05am

Hello,

I have written the below code for capturing the excel content as image, adding new sheet in excel and pasting the image in added new sheet.
Earlier my code for capturing the image was working but now its giving some error. Please let me know the error as i am unable to identify it.
Also the code is creating chart in the image but i dont want it.

' To copy content as image
Sub Export_Range_Images()

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture

Set oRange = Range("A1:I84")
Set oCht = Charts.Add
Set oImg = Picture.Add
oCht.Paste
oCht.Export Filename:="E:\img\SavedRange.jpg", Filtername:="JPG"
End Sub

' To add new sheet
Sub AddSheet()
Dim ActNm As String

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
End With
ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Jul 16 2012"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")
If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0
End Sub

'Code to paste image in newly added sheet
Sub TestInsertPictureInRange()
InsertPictureInRange "E:\img\SavedRange.jpg", _
Range("A1:I84")
End Sub

Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

/*Calling all macros*/
Sub All_codes()
Export_Range_Images
AddSheet
TestInsertPictureInRange
End Sub

Thanks!
Archie

Kenneth Haugland 15-Aug-12 5:11am

I think you should just post a new question :)