Setting cell format based on format codes

[Hans Knudsen]

I have some format codes in G14:G36 like:
G14: #.##0..
G15: _(* #.##0_);_(* (#.##0);_(* "-"_);_(@_)
G16: "+45"## ## ## ##
etc.

and would like a macro that can take the format codes in G14:G36 and create
these formats in cells I14:I36.

Will someone help me?

Regards
Hans Knudsen

 

[Rick Rothstein]

If I understand your request correctly, this macro should do what you
want...

Sub ApplyFormats()
Dim C As Range
For Each C In Range("G14:G36")
C.Offset(0, 2).NumberFormat = C.Value
Next
End Sub

 

[Hans Knudsen]

Rick Rothstein
Thank you.
Yes you understood me correctly, but there are some problems, which I think
is due to the fact that my location is Denmarkand as you may (not) know we
use

.. (point) as thousand separator
, (comma) as decimal separator

For example, when I run your macro on cells with the folllowing formats:
#.##0,00 "kroner" and
#.##0 "kroner"
I get the results
1500,000 kroner

1500,0 kroner


Date formats: For day, mont and year we use "dd-mm-åååå" where I guess you
would use "dd-mm-yyyy", that is å instead of y. For example the code
"mmm-åå" becomes nov åå after running your macro on a cell containg a date
i November.

I did not think of all these problems when I asked the question., and I
wonder if it is somewhat too involved.

Hans

 

[Rick Rothstein]

I'm afraid I have no experience with international formatting issues, so I
can't address what you have said directly; however, it is my belief
(although I could be wrong) that if you use locally correct formatting
pattern strings in your cells, then the code I posted should work. I don't
know what to say beyond that. Perhaps someone with more international excel
experience will come along and add to my comments. It's the weekend, so the
number of volunteers answering questions is small right now; so I would say
if you don't hear anything by Tuesday, repost your question (mentioning the
international formatting angle) and someone should be able to answer it
then.