Thread: Sheet Map

The attachment generates a new worksheet that maps the contents of the active sheet by cell type:

@ for literal text
N for a literal number
n for a number stored as text
B for a literal Boolean
F for a formula
< for a formula the same as that at left
^ for a formula the same as that above
+ for a formula the same as those above and left

Code goes in a code module, run SheetMap.

I take no credit for conceiving this (and will credit whoever did if I find out), just the 15 minutes it took to write. It is minimally tested.

Example output:

       A B C D E F G H I J K L M N O P Q R S T U V W X Y
   1       @                                            
   2                                                    
   3       @                           @ N @            
   4                                   @                
   5               @             @     @ @ @ @ @ @ @ @ @
   6   @   @ @ @   @ @   @ @ @   @ @   N N N N N N N N @
   7   F   @ F F   F <   @ F F   F F   F < < < < < < <  
   8   ^   @ ^ ^   ^ +   @ ^ ^   ^ ^   ^ + + + + + + +  
   9   ^   @ ^ ^   ^ +   @ ^ ^   ^ ^   ^ + + + + + + +  
  10   ^   @ ^ ^   ^ +   @ ^ ^   ^ ^   ^ + + + + + + +  
  11   ^   @ ^ ^   ^ +   @ ^ ^   ^ ^   ^ + + + + + + +  
  12   ^   @ ^ ^   ^ +   @ ^ ^   ^ ^   ^ + + + + + + +  
  13   ^   @ ^ ^   ^ +   @ ^ ^   ^ ^   ^ + + + + + + +

EDIT: Maybe this does the same thing John Mote's Formula Mapper does (I haven't looked). Broro has posted it in http://www.excelforum.com/excel-gene...er-add-in.html.

EDIT2: This one doesn't indicate dates stored as text. That should be fixed.

Modest improvements:

'   @ for literal text

'   # for a literal number                              (yellow if stored as text)

'   B for a literal Boolean

'   D for a Date                                        (yellow if stored as text)

'   F for a formula                                     (red if error)
'   < for a formula the same as that at left            (red if error)
'   ^ for a formula the same as that above              (red if error)
'   + for a formula the same as those above and left    (red if error)