<DMcRitchie@msn.com>De: David McRitchie 
Objet: Re: newbie needs macro to learn- thankyou
Date : mardi 19 octobre 1999 00:56

Hi Sunil,
I guess you specifically asked to learn from a macro.
Hopefully you'll get or already received one or two more examples.

Take a look at John Walkenbach's
    http://www.j-walk.com/ss/excel/tips/tip62.htm
it generates a function.

You can convert it to a subroutine to something like the following
that places the a cell type in the next cell to the right of the
selected cells.  There is a test for lastcell should you select
an entire column.

Option Explicit
Sub showtype()
  'showtype in cell one cell to the right
  'based on http://www.j-walk.com/ss/excel/tips/tip62.htm
dim Celltype As String
dim c As range, lastcell As range
set lastcell = cells.Specialcells(xlLastCell)
application.screenupdating = false
with selection
for each c In selection
  if c.Row > lastcell.Row then Goto done
  select Case true
        Case IsEmpty(c): Celltype = "Blank"
        Case application.IsText(c): Celltype = "Text"
        Case application.IsLogical(c): Celltype = "Logical"
        Case application.IsErr(c): Celltype = "Error"
        Case IsDate(c): Celltype = "Date"
        Case InStr(1, c.Text, ":") <> 0: Celltype = "Time"
        Case IsNumeric(c): Celltype = "value"<sunil@sunil.freeserve.co.uk>
  end select
  c.offset(0, 1) = Celltype
next
end with
done:
application.screenupdating = true
end Sub

Note  Date, Time, and value  are all numeric, you could change value to Number
and remove the previous tests for  Date and Time.

HTH,
David McRitchie,  Microsoft MVP - Excel
My Excel Pages:  http://members.aol.com/dmcritchie/excel/excel.htm

Sunil Patel  wrote in message news:#xXnAyaG$GA.236@cppssbbsa05...
>   How would one scan a column for mixed entries i.e text and numbers
>    and if it can be read as a number write "number" next to it,
>    and if not write "text".
>    if number double it and return in col 3
>    if you can trim the cell contents (remove initial spaces) to
>    leave an exact value, then DON'T double ,col 3 = number.
>    Also if empty cell i need to return row number.
>
>    spaces="_" in example below.
>    (if you help please explain code as much as possible)
>
> COL 1      COL 2    COL 3
> ----------+------------+----------
> sunil         text           sunil          text hence returns text in col 3
> A123       text           A123        reads as text hence returns text in
> col 3
> 123         number    246           number (double value in col 3)
> 33           number    66              number hence doubled
> ____      text           spaces     detects spaces c.f empty cell (but text)
>                empty       row 6       empty (not written in)  col 3 =row
> number (6)
>                empty       row 7       empty (not written in)  col 3 =row
> number (7)
> a59          text         a59          can't trim hence returns text
> 12_12     text        12_12       text hence returns text
> __abc     text         abc          CAN TRIM hence trimed text returned in
> col 3
> abc___   text        abc          CAN TRIM hence trimed text in col 3
> 11___     text       11             NOT number hence (text) but not doubled
> ONLY TRIMED
> __22       text       22             NOT number hence (text) but not doubled
> ONLY TRIMED
>
>
>