vba help - cdate

[Mallesh23]

Hi Team,

I am trying to convert a text Range into Date using CDate. but it converts single cell only.
Range("d1:d17").Value = CDate(Range("d1:d17").Value) ... type mismatch any alternate.

Option Explicit

Sub ConvertTextNumber_ToDate()

Range("d1").Value = CDate(Range("D1").Value)

Range("d1:d17").Value = CDate(Range("d1:d17").Value) ... type mismatch


End Sub

Below is a Table.

Book2
	D	E
1	04/08/2012	TRUE
2	08/04/2012	FALSE
3	08/04/2012	FALSE
4	08/04/2012	FALSE
5	08/04/2012	FALSE
6	08/04/2012	FALSE
7	08/04/2012	FALSE
8	08/04/2012	FALSE
9	08/04/2012	FALSE
10	08/04/2012	FALSE
11	08/04/2012	FALSE
12	08/04/2012	FALSE
13	08/04/2012	FALSE
14	08/04/2012	FALSE
15	08/04/2012	FALSE
16	08/04/2012	FALSE
17	08/04/2012	FALSE
Sheet1

Cell Formulas
Range	Formula
E1:E17	E1	=ISNUMBER(D1)

Thanks
mg

 

[Eric W]

Try:

Range("D1:D17").Value = Evaluate("IF({1},D1:D17+0)")

You'll probably want to format the cells as a date.

 

[Mallesh23]

Hi Eric

Thats great ! it worked,

how to read this line= Evaluate("IF({1},D1:D17+0)") or how it works

Thanks
mg

 

[Eric W]

Evaluate is a VBA function that will evaluate an Excel formula. One feature of it is that it handles array formulas by default. So for an IF 0 means False and 1 means True, and you put the 1 in array braces to say it's an array formula, then the True result of the formula is D1:D17+0. If you do arithmetic (+0) on a number formatted as text, it converts it to a number first. Then since it's an array formula, it works on all the values of D1:D17, returns an array, which is then sent to the D1:D17 range.

It's kind of a tricky formulation. It's a 1-liner, and it works, but there is something to be said about a more "standard" VBA way of doing it, like:

    For Each c In Range("D1:D17")
        c.Value = CDate(c.Value)
    Next c

But it's up to you.

 

[Mallesh23]

Hi Eric,

Thank you once again for your help and also explaining logic behind Evaluate. ?


Thanks
mg