VBA Format Function not working

Question

Last week I created this macro that allows me to create a record of my data. It places the data next to the date and is set to replace the date if we are still on the same date (and if not move on to the current date). This is the code:

Sub Record()

'Get all the dates
'Selecting the data to copy
Range("C23:O23").Select
Selection.Copy

'Find the last used row in Column B
Dim LastRow As Range
With ActiveSheet
    Set LastRow = .Cells(.Rows.Count, "B").End(xlUp)
End With

'if still today, replace data, if not record the data on the next line
If LastRow = Format(Date, "dd/mm/yyyy") Then
    LastRow.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, 
Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Else
    LastRow.Offset(1, 0).Value = Format(Date, "dd/mm/yyyy")
    LastRow.Offset(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, 
Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End If

Application.CutCopyMode = False

End Sub

The thing is, that last week it worked perfectly. Today when I turned it on, it seems to put the date in American format (07/02/2018 as opposed of 02/07/2018) even though I set the format in the vba code. Thisa renders the macro useless as it still checks against English date format and so skips a line every time I click the button.

Any help would be very much appreciated.

Before I even begin checking your code, there already are some red flags, such as using Select or ActiveSheet which should be always (if possible - which in this case is) be avoided. Additionally from first glance, it seems like you're mixing Paste and PasteSpecial into some sort weird hybrid — Samuel Hulla
– Samuel Hulla, Commented Jul 2, 2018 at 7:46
If LastRow is a range and points to a cell containing a true date then LastRow = Format(Date, "dd/mm/yyyy") should never be true. If LastRowm points to text-that-looks-like-a-date then fix your data. — user4039065
– user4039065, Commented Jul 2, 2018 at 7:53
It points to text that looks like data and last week it seemed to work perfectly. — user1234
– user1234, Commented Jul 2, 2018 at 7:57
As @Jeeped said. If LastRow = Date should work which suggests your dates aren't being read as dates. It might be a fairly simple tasks to have VBA read them as dates, or to convert the original data to a true date. How are the dates entered onto the sheet? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Jul 2, 2018 at 7:57

Darren Bartrup-Cook · Accepted Answer · 2018-07-02 08:12:58Z

1

Looking through your code I'm wondering if part of the problem is that you're adding dates to the last cell using FORMAT which returns a string.
In place of LastRow.Offset(1, 0).Value = Format(Date, "dd/mm/yyyy") just use LastRow.Offset(1,0) = Date.

To correct the dates already on the sheet you could multiply the values by 1, or add 0 to convert them to numbers rather than text (and then format as dates):

Enter 1 into any empty cell and copy the cell.
Highlight your dates, right-click and select Paste Special.
In the Paste Special dialog box select Multiply and hit OK.

This code should also work if the Paste Special method doesn't, but I don't feel it's solving the root of the problem.

If CDATE(LastRow) = Date Then

answered Jul 2, 2018 at 8:12

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1234 Over a year ago

Last week, when I used just DATE, then it would convert the date into a number but today it seems to be working.Thanks!

Darren Bartrup-Cook Over a year ago

Excel holds the date as a number - it's the number of days since 1st January 1900 so todays date is 43283 - fractions are used for time, so 43283.5 is midday. Just format the cell rather than the value as a date and all should be good - the value a cell holds and what it displays can be very different.

JohnyL Over a year ago

You forgot to mention to set Values rather All in Paste section.

Darren Bartrup-Cook Over a year ago

@JohnyL I'd never thought about selecting just Values. Using values it converted it to a number, but it was still left aligned so surely still being treated as text? When I chose All it returns it right aligned which is where it should be for a number.

Collectives™ on Stack Overflow

VBA Format Function not working

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related