The date in excel and the date in userform is not picked up while uploading the data to another excel sheet

Question

Hi I am trying to upload the Data from the main excel workbook to another database excel workbook using the upload code(Mentioned below). Apparently it seems that the code is not able to pick the Cell.Value (i.e the date value in excel sheet - format of date is M/D/YYYY) and the userform textbox date value - format of date (M/D/YYYY).


Private Sub Upload()
Dim SourceWB As Workbook
Dim SourceWs As Worksheet

Dim DesWB As Workbook
Dim DesWs As Worksheet

Dim DateRange As Range
Dim DesDataRange As Range

Dim LastRowCount As Long                               'Upload Button Value
Dim DesLastRow As Long

Dim Ls As Long
Dim Y As Long
    
Set SourceWB = ThisWorkbook
'Debug.Print SourceWB
Set SourceWs = SourceWB.Worksheets("Database")
'Debug.Print SourceWs
'Debug.Print DesWB
Set DesWB = ActiveWorkbook
'Debug.Print DesWs
Set DesWs = DesWB.ActiveSheet

Workbooks(FileNameValue).Activate
ActiveWorkbook.Worksheets("Sheet1").Range("A2:T9999").ClearContents

LastRowCount = SourceWs.Range("D" & Rows.count).End(xlUp).Row
DesLastRow = DesWs.Range("D" & Rows.count).End(xlUp).Row

Set DateRange = SourceWs.Range("D2", "D" & LastRowCount)

Set DesDateRange = DesWs.Range("D2", "D" & DesLastRow)

'Paste Similar Date Values to destination file
For Each Cell In DateRange                   '(frmData.txtdate.value is the textbox value of the userform)
    If Cell.Value = frmData.txtdate.Value Then **'(It seems to have problem over here)**
    Debug.Print frmData.txtdate.Value
    Debug.Print Cell.Value
            'Y = Cell.Row            'Cells(y, 1), Cells(y, 20)
            SourceWs.Range("A" & 2, "T" & LastRowCount).Copy
            Workbooks(FileNameValue).Activate
            Ls = ActiveWorkbook.Worksheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
            ActiveWorkbook.Worksheets("Sheet1").Range("A" & Ls + 1).PasteSpecial Paste:=xlPasteValues    
Exit For
    End If
Next
    'My Routine:
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            

        
End Sub

It's not a good idea to use things like ActiveWorkbook and ActiveSheet (or any form of Activate or Select for that mater. Have a look at this for more information). Please fully qualify your workbooks and worksheets. Also, once you set your workbooks and worksheets, you then have this statement: Workbooks(FileNameValue).Activate.. is there a third workbook involved? Otherwise you could just use the workbooks that you have already set — Zac
FileNameValue(Workbook3) is another workbook from where the data is copied and pasted in the second workbook (Database Workbook(Workbook2)). The code is in the Main Workbook(Workbook1). — Prathamesh Sable
If FileNameValue is a public variable why didn't you make the object itself such a variable, say, Wb3. Note that ".Range("D2", "D" & LastRowCount)" is wrong syntax. "Range("D2:D"& LastRowCount)" might work or Range(Range("D2"),Range("D"&LastRowCount)) — Variatus

Variatus Variatus · Accepted Answer · 2020-08-20T08:59:09

What does the Cell in DateRange contain? A true date (which is a number) or a text string that looks like a date? The Value is either the text string or the formatted expression of the underlying date number. If you have a true date (a number) Value2 would give that number if it is the result of a calculation (formula) and Formula will return the desired number if a date was entered in the cell and Excel formatted it as a text string.

The TextBox.Value is a text string. Therefore it can be compared to a text string in the cell. If the two strings aren't exactly the same they will be different. Therefore it's safer to have a true date in the cell and convert the string date in the Tbx to a true date which can then be compared with Value2 (which would be the same for either version of the true dates - entered or calculated).

The date in excel and the date in userform is not picked up while uploading the data to another excel sheet

1 Answers