Application TEXT worksheet function

[alwayslrN]

I have the following loop

For Each DQCellRng in Range(Range("N2",Range("N" & DQLast))
  If DQCellRng.Value = "" then
     DQCellRng.Value = ""
  Else
     DQCellRng.Value = Application.Text(DQCellRng.Value,"yyyy-mm-dd")
  End If
Next DQCellRng

The issue is the cells that have a value, 04-03-07, for example stay as is. Why is that? When I test the function as a formula in a cell it works fine.

Thanks

 

[PHV]

You may try this:
DQCellRng.Value = Format(DQCellRng.Value, "yyyy-mm-dd")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Hi PH - I tried that and received Run-Time error 438 Object doesn't support the property or method.

Thanks

 

[PHV]

Which property/method of which object ?

 

[alwayslrN]

Sorry - of the example you had given, using Format. The way I had done doesn't do anything. No errors and I do not get the desired result.

 

[PHV]

Seems that your cell values are not real date ...
How is the column N populated ?
You may consider the CDate or CVDate functions to clean up your data.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

The cell is either blank of in as format that looks like mm-dd-yy in the cell. I looked at the format and under custom it is formatted as [$-409]mm-dd-yy;@. BTW I did not create this spreadsheet.

 

[alwayslrN]

Oh I forgot to mention I am using Excel 2002 and that in the input bar to the left of fx the value reads as 04/24/2007, but in the cell as 04-24-07. I want to make it text so when the file is saved as a csv it reads as 2007-04-24 and not the julian (or whatever the techical name is called) number.

 

[PHV]

Why not simply replace mm-dd-yy with yyyy-mm-dd in the cell format ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

I was just looking into that. The macro is doing a copy paste from one spreadsheet to another. I think I inadvertantly copied the format from the other spreadsheet into mine. I am trying to clean that up now.

 

[alwayslrN]

I still need the value to save as text otherwise when I save the file as csv the value will save as 39175 and I do not want that.

I changed the cell formating in my spreadsheet and I changed the code to the following and I am still not getting the value to change to text.

DQCellRng.Value = Application.WorksheetFunction.Text(DQCellRng.Value,"yyyy-mm-dd")

 

[PHV]

And what about this ?

DQCellRng.Value = Format(DQCellRng.Value, "'yyyy-mm-dd")

Notice the single quote to force a literal value.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

That did it - thanks PH I really appreciate it. A