Here's a puzzler...!

[red49er]

How to differentiate from within VB code module, between Excel 'cell types', i.e. 'general', 'numeric', 'text', 'formula' etc.?
Example:
Test and return a difference between;
"1234" in Excel 'general' format cell
and
"1234" in 'numeric' format cell

IsNumeric can't tell the difference.
I think I need to look at special Excel functions actioned from VBmodule??

Any help gratefully received...

[Bryan Hessey]

How to differentiate from within VB code module, between Excel 'cell types', i.e. 'general', 'numeric', 'text', 'formula' etc.?
Example:
Test and return a difference between;
"1234" in Excel 'general' format cell
and
"1234" in 'numeric' format cell

IsNumeric can't tell the difference.
I think I need to look at special Excel functions actioned from VBmodule??

Any help gratefully received...

Can you retrieve .NumberFormat = ?

(that is the characteristic, I know it can be set, but have no idea how to retrieve it)

otherwise there were some comments by SteveG in the post http://www.excelforum.com/showthread.php?t=577126 regarding XLM Macro Language that might help.

Please Login or Register  to view this content.

works to test for format TEXT
---

[red49er]

Quote:

Bryan,

Yes, you put me on the right road, that's great thanks.

The problem was to update several 'Price List' sheets.
The trouble was that the Prices(numbers) are in a matrix,
with numbers for 'Width' across the top and numbers for 'Height' down the side.
I had already changed the cell format of the Prices to 'Number' (leaving all other cell formats in 'General'), but IsNumerical could not differentiate.

So whenever I increased my prices from a Module I increased the 'sizes' as well...!


Anyway thanks again, and the code fragment below may help others scanning a number matrix or whatever.

As you can see it is easy to retrieve and test the returned result which is zero for cell format types 'Number' and 'Scientific'. All other format types also give a specific return, so are easy to filter.


Code:

Sub TestCellFormat()
With Sheets("sheet1")
Range("a1").Select

temp = Selection.NumberFormat ' assign or
If temp = False Then
MsgBox Selection.NumberFormat ' access directly
Else: MsgBox "Type is " + temp
End If

End With

End Sub

[Bryan Hessey]

Quote:

Bryan,

Yes, you put me on the right road, that's great thanks.

The problem was to update several 'Price List' sheets.
The trouble was that the Prices(numbers) are in a matrix,
with numbers for 'Width' across the top and numbers for 'Height' down the side.
I had already changed the cell format of the Prices to 'Number' (leaving all other cell formats in 'General'), but IsNumerical could not differentiate.

So whenever I increased my prices from a Module I increased the 'sizes' as well...!


Anyway thanks again, and the code fragment below may help others scanning a number matrix or whatever.

As you can see it is easy to retrieve and test the returned result which is zero for cell format types 'Number' and 'Scientific'. All other format types also give a specific return, so are easy to filter.


Code:

Sub TestCellFormat()
With Sheets("sheet1")
Range("a1").Select

temp = Selection.NumberFormat ' assign or
If temp = False Then
MsgBox Selection.NumberFormat ' access directly
Else: MsgBox "Type is " + temp
End If

End With

End Sub

Good to see, and hope the code you supplied might help others.
---