PDA

View Full Version : Solved: Problem in using Custom Format through VBA



msuresh
03-19-2012, 02:58 AM
Hi,

I am trying to change a cells display format through custom format option in a workbook. It works like this --a macro will change the target cell's display based on adjacent cells value.

In attached model in Cell “H9” has a value of 2000. Its adjacent cell has a text as “LTM”. By using a macro code I want to change the display format of H9 as LTM. But macro is taking “M” in “LTM” as month for the year 2000 and changing display format as “LT6”.

Please help me how to avoid excel reading “M” as month.

Advance Thanks,
Suresh

Bob Phillips
03-19-2012, 03:40 AM
There is nop suh format as LTM that I am aware of. How do you want H9 to show as?

msuresh
03-19-2012, 03:45 AM
Hi Xld,

I want to show Cell H9 as "LTM" or "FY"

Bob Phillips
03-19-2012, 05:14 AM
Sub Custom_Format()

Dim x As String
Dim rng As Range

Set rng = Range("H9")

x = """" & rng.Offset(0, 1).Value & """"
rng.NumberFormat = x

rng.Select
End Sub

msuresh
03-19-2012, 05:51 AM
Thanks Xld, It is working fine.:clap:

msuresh
03-19-2012, 06:01 AM
How can name this thread as "solved". I am not seeing any buttons.

Bob Phillips
03-19-2012, 06:11 AM
Above the threads is a Thread Tools dropdown. It is there.