VBA/Macro/Date format help

[Jock]

Hello

Firstly I apologise if this is not the right forum.

Some kind soul (I forget who, sorry) wrote me a macro that copied the
contents of 4 columns to another part of a spreadsheet if the date matched
today's date. For some reason I can't fathom, when it copies the date, it
does so in US format rather than the UK format which is the setting for my
PC. Given that today's date (2nd August) in UK reads as 02/08/03 the US
format returns the date of Feb 2nd. I really don't understand VBA at all,
when I call the help file Excel can't locate it, and I'm struggling!

I can post the macro if required, but wondered whether anyone had
encountered and/or resolved this problem before.

Thanks,

Stewart Brough

 

[Tom Ogilvy]

You also might try:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("AD5", .Cells(.Rows.Count, "AD").End(xlUp))
'Set myRng = .Range("ad5:AD556")
For Each myCell In myRng.Cells
If myCell.Value = Date Then
.Cells(myCell.Row, "AH").Resize(1, 4).Value _
= myCell.Resize(1, 4).Value
.Cells(myCell.Row,"AH").Numberformat = "dd/mm/yyyy"
End If
Next myCell
End With

End Sub

 

[Jock]

Dave/Tom

Thankyou both very much, the latest one you provided Dave, works a treat,
and I'm once again indebted to you.

I had actually devised a workaround using the initial macro, by formatting
the original date-to-be-copied simply as general (ie the serial number) it
worked.

Thanks once again

Jock

 

[Tom Ogilvy]

You didn't say whether the change I suggested worked or not?

--
Regards,
Tom Ogilvy

Dave/Tom

Thankyou both very much, the latest one you provided Dave, works a treat,
and I'm once again indebted to you.

I had actually devised a workaround using the initial macro, by formatting
the original date-to-be-copied simply as general (ie the serial number) it
worked.

Thanks once again

Jock

My next guess!

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("AD5", .Cells(.Rows.Count, "AD").End(xlUp))
'Set myRng = .Range("ad5:AD556")
For Each myCell In myRng.Cells
If myCell.Value = Date Then
myCell.Resize(1, 4).Copy
.Cells(myCell.Row, "AH").PasteSpecial Paste:=xlPasteValues
End If
Next myCell
End With

application.cutcopymode = false
End Sub


It just does the paste special values. But if that doesn't work, post back and
one of the international users can jump in. I'm out of ideas!

Good luck,

original

 

[Tom Ogilvy]

I forgot to add, if my suggestion doesn't work, could you try it with this:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("AD5", .Cells(.Rows.Count, "AD").End(xlUp))
'Set myRng = .Range("ad5:AD556")
For Each myCell In myRng.Cells
If myCell.Value = Date Then
.Cells(myCell.Row, "AH").Resize(1, 4).Value _
= myCell.Resize(1, 4).Value2 '<== note the use of
Value2
.Cells(myCell.Row,"AH").Numberformat = "dd/mm/yyyy"
End If
Next myCell
End With

End Sub

Don't care what solution you use, but would like to know what works. Thanks
in advance.

Regards,
Tom Ogilvy

Dave/Tom

Thankyou both very much, the latest one you provided Dave, works a treat,
and I'm once again indebted to you.

I had actually devised a workaround using the initial macro, by formatting
the original date-to-be-copied simply as general (ie the serial number) it
worked.

Thanks once again

Jock

My next guess!

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range

With Worksheets("sheet1")
Set myRng = .Range("AD5", .Cells(.Rows.Count, "AD").End(xlUp))
'Set myRng = .Range("ad5:AD556")
For Each myCell In myRng.Cells
If myCell.Value = Date Then
myCell.Resize(1, 4).Copy
.Cells(myCell.Row, "AH").PasteSpecial Paste:=xlPasteValues
End If
Next myCell
End With

application.cutcopymode = false
End Sub


It just does the paste special values. But if that doesn't work, post back and
one of the international users can jump in. I'm out of ideas!

Good luck,

original

 

[Dave Peterson]

I've seen posts about using value2 with currency. And I just read the help.
Thanks for the tip.

Another "trick" is to use the Value2 property of the cell rather than Value. Dates are
transferred as the serial number. Then you format the receiving cell afterwards.