Hi,

I want to be able to detect the format of a formula or value in a cell and
particularly if it has percentage format.

I can't find a clever VBA statement to detect the cell format so I thought
about testing the cell value using "Instr" to see if the "%" was the last
character in the string. However when I write a VBA statement to test the
variable it restates the value of the cell from %'s to a decimal (e.g. 5%
becomes 0.05) thus eliminating the % sign I'm attempting to detect...

Ideas most welcome...Chris

Hi Chris,

Use the NumberFormat property,
like in
dbug.print[a1].numberformat

--
Kind regards,

Niek Otten

"Chris Gorham" wrote in message
...
Hi,

I want to be able to detect the format of a formula or value in a cell and
particularly if it has percentage format.

I can't find a clever VBA statement to detect the cell format so I thought
about testing the cell value using "Instr" to see if the "%" was the last
character in the string. However when I write a VBA statement to test the
variable it restates the value of the cell from %'s to a decimal (e.g. 5%
becomes 0.05) thus eliminating the % sign I'm attempting to detect...

Ideas most welcome...Chris

Make that

Debug.Print Range("a1").NumberFormat

--
Kind regards,

Niek Otten

"Niek Otten" wrote in message
...
Hi Chris,

Use the NumberFormat property,
like in
dbug.print[a1].numberformat

--
Kind regards,

Niek Otten

"Chris Gorham" wrote in message
...
Hi,

I want to be able to detect the format of a formula or value in a cell
and
particularly if it has percentage format.

I can't find a clever VBA statement to detect the cell format so I
thought
about testing the cell value using "Instr" to see if the "%" was the last
character in the string. However when I write a VBA statement to test the
variable it restates the value of the cell from %'s to a decimal (e.g. 5%
becomes 0.05) thus eliminating the % sign I'm attempting to detect...

Ideas most welcome...Chris

As Niek said, you can use the numberformat property which is probably the
best approach. If you know there will be a value in the cell, then you
could use the Text property which provides a string representation of how
the cell appears or displays its value.

If right(activeCell.Text,1) = "%" then

or

If Instr(1,Activecell.Text,"%",vbTextcompare) then

--
Regards,
Tom Ogilvy


"Chris Gorham" wrote in message
...
Hi,

I want to be able to detect the format of a formula or value in a cell and
particularly if it has percentage format.

I can't find a clever VBA statement to detect the cell format so I thought
about testing the cell value using "Instr" to see if the "%" was the last
character in the string. However when I write a VBA statement to test the
variable it restates the value of the cell from %'s to a decimal (e.g. 5%
becomes 0.05) thus eliminating the % sign I'm attempting to detect...

Ideas most welcome...Chris