0

I'm interested in why why following line ("Sheets("Euro_usd").Columns("A").End(xlDown).Offset(1, 0).Value = EURO_USD.t_date") causes an

"Application defined or object defined error"

Replacing the code with the equivalent : Sheets("Euro_USD").Cells(rows.Count, "A").End(xlUp).Offset(1, 0).Value = EURO_USD.t_date makes the error to dissapear.

Why is the error appearing? Does it have to do with that the end(xldown) for columns property is already occupied in the stack( not sure on terminology ) and thus the new request is generating an error? 

Private Sub run() ' run the whole operation

Dim HTTP_Req As Object: Set HTTP_Req = New HTTP_Req
Dim EURO_USD As Object: Set EURO_USD = New EURO_USD


If Sheets("EURO_USD").Columns("A").End(xlDown).Value = DateValue(EURO_USD.t_date) Then
    Debug.Print "Date already exist"
    Else
    Sheets("Euro_usd").Columns("A").End(xlDown).Offset(1, 0).Value = EURO_USD.t_date <-- Error here!

End If

End Sub
Community
  • 1
  • 1
uncool
  • 2,613
  • 7
  • 26
  • 55
  • Find the last row and then add the value. You may want to see [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) Also to check if a date is in a column or not, you can use `Application.WorksheetFunction.CountIf()` – Siddharth Rout Oct 02 '14 at 00:30

1 Answers1

1

If column A is empty, this statement tries to write in the row after the maximum row in the sheet which is impossible. Check it by printing Sheets("Euro_usd").Columns("A").End(xlDown).Row. It is probably equal to Sheets("Euro_usd").Rows.Count (which gives you the maximum row number).

The second statement (xlUp) will result in the first row if column A is empty thus writing the value in the second row (Offset(1,0)).

lokusking
  • 7,396
  • 13
  • 38
  • 57
Barry
  • 3,683
  • 1
  • 18
  • 25
  • Well, the thing is that Column A is not empty... thus writing shets("Euro_usd").Columns("A").end(xldown).row returns 3 – uncool Oct 03 '14 at 22:11
  • What is the value and type of EURO_USD.t_date when you write this value? – Barry Oct 04 '14 at 07:40
  • Also try if you can set Sheets("Euro_usd").Columns("A").End(xlDown).Offset(1, 0).Value = Date. All this just for trying to find out which part of the statement raises the error. – Barry Oct 04 '14 at 07:49
  • Thank you for taking your time help me out. the = date statement works correctly. – uncool Oct 04 '14 at 22:08