user5538704 user5538704 - 1 year ago 60 Question

Date Check in

I have a problem where in excel one column contains a unique number in the form : mmdd followed by serial number 01,02.. For eg: 101201(10:month, 12:today's date, 01:first entry), 101202.
Now, what I need is that my form in should take the last entered data (for eg: 101202) check if it is today's date. If yes, then it should add 1 and display it in a message box.(yes, then 101203 should be displayed). If not, then it should take current date and start with 01 (101301 in case, the date is 13/10/2016).
I managed to get the data from previous row. But how should I check it with date?
Please help!

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
xlWorkBook = xlApp.Workbooks.Open("C:\Users\Desktop\testing.xlsx")
'xlApp.Visible = True
xlWorkSheet = xlWorkBook.Sheets("Sheet1")

Dim row As Long
row = 1
With xlWorkSheet
While .Cells(row, 2).value IsNot Nothing
row = row + 1
End While
'MsgBox(row - 1)
End With

Dim r As Excel.Range
Dim t As String = Format(Today, "MMdd")
If xlWorkSheet.Cells(row - 1, 4).value Is Nothing Then
Me.txtQuote.Text = t & "01"
r = xlWorkSheet.Cells(row - 1, 4)
Me.txtQuote.Text = (r.Value + 1)
End If

'this is wrong and I know it's wrong
If r.Value = Date.Today Then
End If

End Sub

Answer Source

Put this where you have written 'this is wrong and I know it's wrong

Dim rDate As String = CStr(r.Value).Substring(0, 4)
If rDate = t Then
End If

rDate basically grabs the first 4 digits from r so now you can compare this to todays date. I have also replaced todays date with t because you have t already using today's date in the required format.

Also naming variables as t and r makes it hard to understand what they are used for.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download