Solved: Cdate in range form number => date

[domm1988]

How to convert number values to date format (2 diffrent cases in 2 separate columns). I only change format of cell. It does not takes values and converts them to date format.

Got 2 columns
11.05.09
11.07.13

and other one
2011.05.09
2011.07.13


MY CODE :
Sub TextBox1_Click()
Dim c As Range
For Each c In Selection.Cells
c.Value = CDate(c.Value)
c.NumberFormat = "m/d/yyyy"
Next c
End Sub


Thanks a lot

[Bob Phillips]

Maybe this

Sub TextBox1_Click()
Dim c As Range
For Each c In Selection.Cells
c.Value = CDate(Replace(c.Value, ".", "/"))
c.NumberFormat = "m/d/yyyy"
Next c
End Sub

[domm1988]

Gr8

Thanks so much :)

[domm1988]

say columns might have 20 or 30 or 50 rows.
Need to have both columns changed up to where values are.
(so it will not be selected cells any more that are being changed)

Thanks a lot

[Bob Phillips]

Dim c As Range
For Each c In Range(Range("A2"), Range("A2").End(xlDown).Cells
c.Value = CDate(Replace(c.Value, ".", "/"))
c.NumberFormat = "m/d/yyyy"
Next c

[domm1988]

line: For Each c In Range(Range("A2"), Range("A2").End(xlDown).Cells
Appears red
compiler says "syntax error"
( i assigned macro to text box. And in first row of columns got labels with text not in data format )

[Bob Phillips]

Sorry my error

Dim c As Range
For Each c In Range(Range("A2"), Range("A2").End(xlDown))
c.Value = CDate(Replace(c.Value, ".", "/"))
c.NumberFormat = "m/d/yyyy"
Next c

[domm1988]

Works gr8

Appriciate it a lot

[domm1988]

How to improve code so it works until last cell with data. Even though some rows in between are empty ?

[Bob Phillips]

Dim c As Range
Dim lastrow As Long

For Each c In Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
If c.Value <> "" Then
c.Value = CDate(Replace(c.Value, ".", "/"))
c.NumberFormat = "m/d/yyyy"
End If
Next c

[Aussiebear]

Try the following
Dim c As Range
For each c in Range(Range("A2") & Rows.Count).End(xlUp).Row
c.Value = CDate(Replace(c.Value, ".", "/"))
c.NumberFormat = "m/d/yyy""
Next c

[domm1988]

Errora msg "for each may only iterate over a collection object or an array in your case"

BUT previous works just fine

I would just remove Dim laast row as long
Caouse it is not used

Thank so much. I start learning and i had assigment for today so it saved my life :)

[Bob Phillips]

Yeah, I was going to calculate the last row and use that in a resize, but thought better of it.

[domm1988]

c.Value = CDate(Replace(c.Value, ".", "/"))
I dont get what happens inside Replace ? Seems (1,2,3) where 1 is a value, 2 dot , and 3 ??? and why like that any way ?

And what ("A" & rows.count) does and why without it want work

Thanks i try to learn VBA

[Bob Phillips]

It is taking a string line 12.03.2012 and changing it to 12/03/2012 and then making sure that is cast as a date.

Range("A" & Rows.Count).End(xlUp) takes the very last cell in column A, Rows.Count, and works back until it finds some data, End(xlUp).