Date duplicates: Adding a counter for a specific row on today's date (VBA/Qlik Sense)

Question

I recently wrote a fairly simple bit of VBA code so that I can keep long-term track of what I am wearing each day. For this I made a Macro connected to a Command Button, and it looks like the following:

    Private Sub WearingToday_Click()

      Dim NextCol As Long
      NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
      Cells(1, NextCol).Value = Date
      Cells(ActiveCell.Row, NextCol).Value = "1"

    End Sub

The code simply adds today's date on the next available column, under which it adds the number "1" on the row which I've chosen.

My problem is, though, that if I activate the macro more than once a day, the date serial number of the second activation will have an additional 1 in the end, giving me a date corresponding to the year 3000-something. To make it clearer: I have worn two things on November 20th, which gives two adjacent columns with the same date; but only the first column of these dates shows the correct serial number.

I might mention that I'm importing the data into Qlik Sense, where I later will do some visualisations. I see the date serial numbers upon storing the data as a QVD-file and looking at it from QViewer.

The interesting thing is that the string representation of both serial numbers -- in the Excel spreadsheet -- is correct. As such, I am not sure if the problem indeed only exists as a consequence of the transition from .xlsm to .qvd, or if it is there from the beginning.

I hope I have been clear with my issue and that someone will be able to help me fix this, or at least make me understand what possibilites I have going forward.

Thanks in advance.

Answer 1

The following code will check to see if the last column heading is already today's date and, if so, just update the same column:

Private Sub WearingToday_Click()

    Dim LastCol As Long
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    If Cells(1, LastCol).Value2 < Date Then
        LastCol = LastCol + 1
        Cells(1, LastCol).Value = Date
    End If
    Cells(ActiveCell.Row, LastCol).Value = 1

End Sub

I suspect that your export to Qlik Sense is being confused by having two columns with the same header (i.e. the date), and it might just be appending a "1" to the second header to make each header unique. But I have never heard of, or seen, Qlik Sense so I can't be sure. If that is the cause of the weird behaviour in Qlik Sense, then ensuring you don't have duplicate headers should make your problems disappear.