Excel Vba runtime error?

Question

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

I'd guess on your first error, you are using x1 in a scope it is not defined. Unless x1 is a global (probably not), you need to pass a reference to it in the sub's declaration. — Joe
– Joe, Commented Feb 19, 2013 at 7:22

CuberChase · Accepted Answer · 2013-02-19 08:52:31Z

4

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;@"

edited Feb 19, 2013 at 8:52

answered Feb 19, 2013 at 7:22

CuberChase

4,5185 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taotao · Accepted Answer · 2013-02-19 07:19:41Z

0

You can use the Format function:

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

answered Feb 19, 2013 at 7:19

Taotao

1647 bronze badges

Comments

Siddharth Rout · Accepted Answer · 2013-02-19 07:40:19Z

0

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

edited Feb 19, 2013 at 7:40

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Feb 19, 2013 at 7:20

user2063626

Collectives™ on Stack Overflow

Excel Vba runtime error?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related