CODEWITHSUNDEEP
vbaexcel
Rama Moorthy
Rama Moorthy

Reputation: 565

Excel Vba runtime error?

When I tried to execute the below code, I got the error message as "Run-time error '424' Object Required",

Sub test()
Range("Q" & i).Text = x1.Text
End Sub

Where x1 is calender object.

If I would use the below code as

Range("Q" & i) = x1.Text

My code would store the date as 02-11-2013 instead of 11-02-2013, the problem here is it re-arranges it month and date.

Hope I will have the solution for this.

Thanks in advance

Upvotes: 3

Views: 1341

Answers (3)

CuberChase
CuberChase

Reputation: 4518

You are getting an error in the first instance since the .Text property of a range is read-only and is used to get the current formatted value from a cell/range.

I'd use the format function but also set the number format of the cell you're putting it into to be safe:

Range("Q" & i).Value = Format(x1.Text, "dd-mm-yyyy")
Range("Q" & i).NumberFormat = "dd-mm-yyyy;@"

Edit: Further to you comment, I'd suggest trying to Format the incoming date with the day and month swapped and then applying the NumberFormat like so:

Range("Q" & i).Value = Format(x1.Text, "mm-dd-yyyy")
Range("Q" & i).NumberFormat = "dd-mm-yyyy;@"

Upvotes: 4

user2063626
user2063626

Reputation:

Kindly Use Range("Q" & i) = Format(x1.Text,"dd-MM-yyyy")

Upvotes: 0

Taotao
Taotao

Reputation: 168

You can use the Format function:

    Range("Q" & i).Value = Format(x1.Text, "dd-mm-yyyy")

Upvotes: 0

Related Questions