Text to Column in VBA not working

[JoeC1]

Hi,

I am trying to use Text to column in VBA but when i run through a macro it doesn't seem to perform the function. I am using this so dates are in the right format for conditional formatting in other cells. below is some code. the amount of rows in the column can vary so i have used

Range(Selection, Selection.End(xlDown)).Select .....as per below. any help appreciated. even when i record this i get the same thing and it works when not run through the macro.



Range("X25").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("X25"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

 

[XLRATOR]

Hello there,
I am not sure of getting what you intend to do.
Are you trying to split the content of Range("X25") into two parts as First and last name?

 

[JoeC1]

Hi,

hmm no. basically i have conditional formatting in some sells that look to see if 2 dates are the same. as these dates are pulled in from 2 separate files the format of the dates are different ( i believe one is set as text and one isnt). when i click the Text to column button (not in a macro) this seem to rectify my conditional formatting issue and the conditional formatting then registers that the 2 dates are the same. does this make sense?

 

[JoeC1]

i forgot to say that i am doing this over a a number of rows in 1 column and when i manually click Text to column i click finish straight after. this then just seems to convert the dates and replace them back into their original cell but in the correct format for my conditional formatting. thanks

 

[XLRATOR]

For text format cells, you may use a formula called: DATEVALUE it actually converts dates stored as text into absolute date values.
After using this formula you will be able to perform your date comparisons as you wish.
I think that would be it.
Regards.

 

[JoeC1]

I tried this before but as some of the dates seem to have already had text to column applied (from source files)/ the format is different when i use DATEVALUE for some dates it just returns #VALUE!. As these formats seem to be inconstant the only thing i have found that works is the Text to col button, but not in my macro.

Thanks

 

[XLRATOR]

Try to perform a concatenation to those columns then apply the formula on the resulted values.