Tests and Notes on Range objects in Excel Cell

**DocAElstein** · 08-01-2023, 06:17 PM

Some extra notes and experiments related this forum Page
https://www.excelfox.com/forum/showt...s-etc-)/page58
https://www.excelfox.com/forum/showt...cel-Cell/page8
https://www.excelfox.com/forum/showt...ll=1#post21923
https://www.excelfox.com/forum/showt...ge58#post21923

Some quirks with Excel Worksheet things ISTEXT and ISNUMBER https://support.microsoft.com/en-au/...1-f2d869135665

Only by chance I noticed a while later that I was lucky that my solution worked here https://eileenslounge.com/viewtopic....309137#p309137
My answer in that Thread was an attempt to get rid of that Number stored as Text warning thing, https://i.postimg.cc/X7hZDNDg/Number-stored-as-text.jpg
https://i.postimg.cc/5NjY3Qp0/Number-stored-as-text.jpg

In the second worksheet in the uploaded file we have the main test data range from Chris, but to simplify this post I will just use a small section, A6:B7 , and do some simple Excel formulas initially involving ISTEXT and ISNUMBER
Initially results are as expected, or at least in a variation of one of the possibilities that we might have expected: The problem cell, A6, is seen as TEXT or not as a NUMBER
https://i.postimg.cc/6psVSh8q/ISTEXT...-initially.jpg
ISTEXT and ISNUMBER seems OK initially.JPG

The next set of results however show a slight anomaly. It would appear that in the larger formula pairs, the one of the pair which would effectively be doing just =A6 makes some change, bit only half way: The warning thing is gone, but the alignment has not changed ( The formula in the pair contain the *2 , I just did to check what of the two options after the IF was being selected. The results there confirm that the involving ISTEXT and ISNUMBER is behaving inside the n as it did on its own )
https://i.postimg.cc/7YJMmRYj/ISTEXT...iour-in-IF.jpg
ISTEXT and ISNUMBER behaviour in IF( , , ).JPG

In fact a simple cell reference exhibits a similar behaviour https://i.postimg.cc/3Nf7LBYx/A6.jpg

So a simple reference seems to be doing something strange, maybe getting sort of half way there.

Before going any further, we should perhaps try the same experiments in Evaluate(" ") , since things do not always work exactly the same in worksheet cells and in Evaluate(" ") : There are sometimes minor differences.

How does the function formulas behave in Evaluate(" ")
The spreadsheet looks similar, (the small difference is explained by VBA working in English, recognising the text I used as a Boolean at some point, possibly, perhaps at point of insertion, which my German Excel then displays in the appropriate German syntax word)
https://i.postimg.cc/26JkxYYK/Evalua...preadsheet.jpg