0

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
Prathamesh Sable
  • 15
  • 3
  • 1
    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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) 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 Aug 20 '20 at 08:26
  • 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 Aug 20 '20 at 08:31
  • 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 Aug 20 '20 at 08:49

1 Answers1

0

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).

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • The `Cell` In DateRange is a true date (M/D/YYYY) . so how should i make these two values same? Sorry i am a rookie with coding , can you please explain me the process. – Prathamesh Sable Aug 20 '20 at 10:02
  • `CDate(frmData.txtdate.Value)` will convert the text string to a true date if Excel can recognize it as a date ( test with Debug.Print). `CLng(CDate(frmData.txtdate.Value))` will convert the date to a Long. You can then look for that Long value in the Cell. – Variatus Aug 20 '20 at 12:51