To Format date over range of cells in Excel vba

[poornima82]

Hi,

I am trying to format date over range of cells using Format().

Sheets("Details (Deliverables)").Range(Format(CDate(Cells(i, "N")), "dd/mm/yyyy"), Format(CDate(Cells(i, "W")), "dd/mm/yyyy")).Select

I got error for this.I having Excel 2013.
I tried using .NumberFormat but it is not working.
So I am trying to use Format(CDate(Cells(i,"N"),"dd/mm/yyyy") .
This is working fine for single cells like this,
Cells(i, "W").Value = Format(CDate(Cells(i, "W").Value), "dd/mm/yyyy")

But I want to format over range of cells.
Please guide me to do this.
I am not very familar in Excel vba.
For the past 15 days i am trying to automate excel vba normally I am a asp.net programmer with C#.
So please help me out.

Regards,
Poornima

 

[Joe4]

Welcome to the Board!

Note the differences here:

This is working fine for single cells like this,
Cells(i, "W").Value = Format(CDate(Cells(i, "W").Value), "dd/mm/yyyy")

and here:

Sheets("Details (Deliverables)").Range(Format(CDate(Cells(i, "N")), "dd/mm/yyyy"), Format(CDate(Cells(i, "W")), "dd/mm/yyyy")).Select

Mainly, the Format needs to be OUTSIDE of the Range (Cells) statement.

Also, note that this code is NOT formatting the cells as date, it is actually converting the entry to a Text value (the FORMAT function will return a string value).
So, that begs the question, what is your intention here? Is it to:
- Format a range of cells in a valid date format?
- Convert a range of cells to a text string in a certain date-looking format?

Note, if your intention is to perform any sort of date logic or sorting on the range of cells, you will want the first option. That code would look like this:

[COLOR=#333333]Sheets("Details (Deliverables)").Range(Cells(i, "N"), Cells(i, "W"))[/COLOR].NumberFormat = "dd/mm/yyyy"

The other one (second option), would actually be updating the value in each cell (and making it a string at the same time).

 

[poornima82]

Hi Joe4,

Thanks for your reply.

Sheets("Details (Deliverables)").Range(Cells(i, "N"), Cells(i, "W")).NumberFormat = "dd/mm/yyyy"
This is working fine in 2016.But My one excel 2013 ,it is not working while i am trying to automate.
In my laptop I am having 2016,it is working fine but when the same code i used in 2013 in my office not working.
So I prefer Format of to proceed.

If I convert column by column using the below code works fine

Cells(i, "W").Value =Format(CDate(Cells(i, "W").Value), "dd/mm/yyyy")

But I wish to convert over range of cells.
Is it possible to do using Format() function? in order to work in Excel 2013

Regards,
Poornima

 

[Joe4]

That code should work fine on 2013 also. There is nothing special about it, it is very basic code. I only have 2010 and it works on it with no issues.
So if it is not working on the 2013 computer, the issue is most likely not with the code, and due to some other reason (and you may have an issue with ANY code on that computer until you identify the issue).
What kind of error are you seeing on the 2013 computer? Perhaps they are not enabling macros?

If you want to apply the FORMAT function to all the values in that range, you would need to loop through each cell in the range individually.

 

[poornima82]

Thanks for your reply.
I am using macro enabled Excel with .xlsm format.
I am automating excel sheet.
If I click one button set of values should change in excel by running the macro.
All macro run on the module and I got the expected result.
The one that is not working is only with this Number Format.
But in my laptop it works fine.
I don't get any error but the format is not changing.

 

[Joe4]

OK, I think I see the issue. You are using the CDATE function, which probably means that your entries are text, and not dates (the CDATE function converts text to dates). So the number formatting will not work on the cells until the values have been converted to dates first (and I left the CDATE out of my function).

Since you are wanting to apply calculations and not just straight formatting to cells, you will need to loop through the cells.
So if you want to loop through columns "N" through "W" (which are columns numbers 14 to 23), you could do so like this:

Dim myCol as Long
For myCol = 14 to 23
[COLOR=#333333]    Cells(i, myCol).Value =Format[/COLOR][COLOR=#333333](CDate(Cells(i, myCol).Value), "dd/mm/yyyy")
Next myCol[/COLOR]

 

[poornima82]

Hi Joe4,

Thank you very much.
Now only i came to know to navigate through column we can use number.
All my burden reduced.
At last I got it worked..
Once again thanks a lot for your timely help.

Regards,
Poornima

 

[Joe4]

You are welcome!

Yes, when using Cells(row,column) to refer to ranges, you can use a letter or number for the column reference.