CODEWITHSUNDEEP
excelvbareplace
tortoise
tortoise

Reputation: 3

VBA - Replace doesn't work in some cells

I work with some data extracted from a database. In a column, I have a set of data in the format dd.mm.yyyy. I'm using the lines below to try to convert these values to dd/mm/yyyy and format as date. 

.Range(Columns(j), Columns(j)).Replace What:=".", Replacement:="/"
.Range(Columns(j), Columns(j)).NumberFormat = "dd/mm/yyyy"

After executing the code, all the replaces were done, and the cells are formated as date, but some values are left aligned, and some are right aligned. When I active the filter in this column, some values are not correct grouped per year (screenshot below).

Column with filter activated

When I go to a value that seems not to be working (for example, the first 13/01/2015 of the screenshot), it is left aligned. Then, if I double click in the cell and press enter, the cell become right aligned.

I have searched for extra characteres in these cells (blank spaces, for example), but did not find anything.

Do you have any ideas about this problem?

p.s.: sorry for my bad english, I am from Brazil (:

Upvotes: 0

Views: 860

Answers (2)

Andrew
Andrew

Reputation: 7778

Usually this happens when you have dates formatted as text. I have a trick that I use to kill those formatting issues:

  1. Select whole column
  2. Go to Tab DATA
  3. Click on TEXT TO COLUMN
  4. Pick Delimited and click Next
  5. Uncheck all boxes and click Finish
  6. Select whole column
  7. Format it as Short Date

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Text to columns can be used to convert dates from one format to another. Manually, you'd select the range, click Text to Columns > Next > Next > select "Date" and specify the order of day, month, year that the SOURCE data is in, then click OK.

Of course, the same can be done with VBA. Fire up the macro recorder, follow the steps above and study the resulting code.

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
    :="", FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True

Upvotes: 1

Related Questions