Macro for last business date

[Nikhil V V]

Hi All,

Can you please help me to create a macro?

I want to add the Last business date in B3 cell just by running a macro.

Thanks in advance
Nikhil

[Olly]

What is the definition of 'Last Business Date' ?

[Nikhil V V]

Hi Olly,

I mean, for Monday, that last business day was Friday & for Friday its Thursday.

Thanks

[Olly]

I mean, for Monday, that last business day was Friday & for Friday its Thursday.

Ok. Try:

Sub foo()
Select Case Weekday(Date, vbMonday)
    Case 1:  Range("B3").Value = Date - 3
    Case Else:  Range("B3").Value = Date - 1
End Select
End Sub

[Nikhil V V]

This is Great!!Thanks Olly, Below is the code which i have made with the help of recording tool as well the date macro you gave. However this gives me an error as "Cant execute the code in break mode"

Sub Macro3()
'
' Macro3 Macro
'Range("B4").Select
Sheets("Unwind & Reset").Select
Rows("1:1").Select
ActiveSheet.ShowAllData
Sheets("Cash Activity Report").Select
Selection.ClearContents
Sheets("DATA").Select
Cells.Select
Selection.ClearContents
Range("E26").Select
Sheets("Cash Activity Report").Select
Range("A1").Select
Select Case Weekday(Date, vbMonday)
Case 1: Range("B3").Value = Date - 3
Case Else: Range("B3").Value = Date - 1
End Select


End Sub


I am gettiing the error when it reaches Here " ActiveSheet.ShowAllData"

Please help me out to fix this

Thanks

[Olly]

1. Wrap CODE tags around your code when you post.
2. To exit break mode, click the 'reset' button (blue square) on the toolbar in the visual basic editor.
3. There's a lot of unnecessary selecting going on there... Are you trying to clear the contents of ALL cells in worksheets [Cash Activity Report] and [DATA], or only certain ranges (and if only certain ranges, what are those range addresses?)

Here's a rewrite of your code without all that unnecessary selecting - but beware, this will clear the contents of ALL cells in those two worksheets... If this isn't what you want, you need to specify the actual range to be cleared.

Sub Macro3()
Sheets("Unwind & Reset").ShowAllData
Sheets("DATA").Cells.ClearContents
With Sheets("Cash Activity Report")
    .Cells.ClearContents
    Select Case Weekday(Date, vbMonday)
        Case 1: Range("B3").Value = Date - 3
        Case Else: Range("B3").Value = Date - 1
    End Select
End With
End Sub

[Nikhil V V]

Hi Olly,

I want this date format as mm-dd-yyyy for this;

Sub foo()
Select Case Weekday(Date, vbMonday)
Case 1: Range("B3").Value = Date - 3
Case Else: Range("B3").Value = Date - 1
End Select
End Sub


Can you please help me with this. At present this is giving me dd-mm-yyyy

[Olly]

Change the numberformat of cell B3.

And again, wrap CODE tags around code you post...

[JOHN H. DAVIS]

Does this help?

http://books.google.be/books?id=46to...=JKjqUpjwNMPNs

[G e r a r d]

Sub DateText()
    Range("B3").Value = Format(Date, "dd mmm YYYY")
End Sub

[Nikhil V V]

Hi Gerard,

Thanks. But this gives me the current date. I want it like this: for Monday, Last business date is Friday. Likewise for Friday it is Thursday.

Please help me with this.

Thanks