Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

advancing date macro

  Asked By: Dylan    Date: Dec 28    Category: MS Office    Views: 949
  

I'd like to create a macro that searches any file for date fields and
auto-advances them to the next date, more specifically, anywhere in the workbook
where it has March 10, 2005 I want it to change to April 10, 2005 OR Jan 2005 to
Feb 2005. We use a lot of templates and it would be a time saver to have all
the dates updated automatically. How can I do this and then deploy it to be
share with others in my department?

Share: 

 

9 Answers Found

 
Answer #1    Answered By: Khadeeja Malik     Answered On: Dec 28

if you want to understand if the data is date  format, you can use isdate()
function.
Something like;

If isdate(Range("A1").value)=true then
'The Codes
End if

and if you want to change the date to the next month.I prefer this example
which i copy from "MS Visual Basic Help"

Dim FirstDate As Date ' Declare variables.
Dim IntervalType As String
Dim Number As Integer
Dim Msg
IntervalType = "m" ' "m" specifies months as interval.
FirstDate = InputBox("Enter a date")
Number = InputBox("Enter number of months to add")
Msg = "New date: " & DateAdd(IntervalType, Number, FirstDate)
MsgBox Msg

 
Answer #2    Answered By: Bohdana Nonob     Answered On: Dec 28

This code will help you to loop thru. all the files in a particular folder,
you need to tune the code as per your requirement

Sub FindXLSFiles()
Dim lFile As Long
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
.LookIn = "C:\Data"
.SearchSubFolders = False
.Execute SortBy:=msoSortByFileType
If .FoundFiles.Count Then
For lFile = 1 To .FoundFiles.Count
On Error GoTo myerrhand
Workbooks.Open Filename:=.FoundFiles(lFile), UpdateLinks:=0
xbook = ActiveWorkbook.Name
Application.StatusBar = "Now working on " & ActiveWorkbook.FullName
'Here is the line that calls the macro  below, passing the workbook
to it
DoChanges
ActiveWorkbook.Save
Application.DisplayAlerts = False
Windows(xbook).Close

myerrhand:
Windows(ActiveWorkbook.Name).Activate
Next lFile
End If
End With
Application.StatusBar = False
End Sub
'-------------------------------
Sub DoChanges()
With ActiveWorkbook
For Each a In .Worksheets
a.Activate
If ActiveSheet.Name <> "Settings" Then
ActiveSheet.PageSetup.FooterMargin =
Application.InchesToPoints(0.3)
Range("A1:F1").Select
Selection.Merge
With Selection
.WrapText = True
.MergeCells = True
End With
End If
Next
End With
Sheets(1).Select
Application.ScreenUpdating = True

End Sub

 
Answer #3    Answered By: Atid Boonliang     Answered On: Dec 28

I'm more looking for something that can be used to workbook templates. I
wouldn't even mind using VBA to execute a find and replace, but I'm not having
any luck there either. Find & Replace looks for text not date  fields. So, I
need something similiar for dates.

 
Answer #4    Answered By: Hayden Evans     Answered On: Dec 28

Search and replace seems to work if you use the format like 1/3/2006
while you search. The actual format of the cells may be different.
You may also think about linking all the dates to one cell and just
changing that cell. There are also some date  and time functions that
may come in handy such as NOW(), TODAY(), etc.

 
Answer #5    Answered By: Sairah Hashmi     Answered On: Dec 28

Try the macro  below. It will not change your formulae that
referencing cells containing dates and it will not change your date
formats.

Sub Macro1()
'
' Macro to increment all dates in a spread sheet by one month.
'
Dim OldDate As Date
RowNo = Cells.SpecialCells(xlCellTypeLastCell).Row
ColNo = Cells.SpecialCells(xlCellTypeLastCell).Column

For i = 1 To RowNo Step 1
For j = 1 To ColNo Step 1
If Cells(i, j).HasFormula = False Then
If IsDate(Cells(i, j).Value) Then
OldDate = Cells(i, j).Value
Cells(i, j).Value = DateAdd("m", 1, OldDate)
End If
End If
Next j
Next i
End Sub

 
Answer #6    Answered By: Rosa Reynolds     Answered On: Dec 28

I tried the below but received a syntax error when I ran it. Do you know what
could be causing it? I'm working in Excel 2000 and am not sure if I'm applying
the macro  correctly.

 
Answer #7    Answered By: Geldefsman Bakker     Answered On: Dec 28

I hope this could be causing for == replacing with =

 
Answer #8    Answered By: Von Fischer     Answered On: Dec 28

You have quoted "==" instead of "="

Try again and it should meet your requirement.

Hope you enjoy using Macro.

 
Answer #9    Answered By: Lenora Green     Answered On: Dec 28

In your quoted code you have lots of doubled equal signs - i.e. ==

I don't know how they got there, but they should all be single equal signs.

 
Didn't find what you were looking for? Find more on advancing date macro Or get search suggestion and latest updates.




Tagged: