GET.CELL(4, What is TYPE?

[Excelente]

Below is the information out of the help for the GET.CELL() function but I'm looking for the equivalent test in VBA for the type_num = 4 situation. I see that #4 below is "4 Same as TYPE(reference)." But what does this mean? VBA has test for DataType, but thats good for PivotTables. I want to test a cell when it has general formatting to know if the data in the cell is a "date", or number, or string, or whatever. What are all of the datatypes that a cell can have when formatted in the General Format?

Any thoughts?

================
Here's the first part out of the help. The same help file has NOTHING in it for TYPE(reference). ugh!
================

Macro Sheets Only
Returns information about the formatting, location, or contents of a cell.
Use GET.CELL in a macro whose behaviour is determined by the status of a
particular cell.

Syntax

GET.CELL(type_num, reference)
Type_num is a number that specifies what type of cell information you
want. The following list shows the possible values of type_num and the
corresponding results.

Type_num Returns

1 Absolute reference of the upper-left cell in reference, as text in the
current workspace reference style.
2 Row number of the top cell in reference.
3 Column number of the leftmost cell in reference.
4 Same as TYPE(reference).
5 Contents of reference.
6 Formula in reference, as text, in either A1 or R1C1 style depending on the
workspace setting.
7 Number format of the cell, as text (for example, "m/d/yy" or "General").
8 Number indicating the cell's horizontal alignment:
1 = General
2 = Left
3 = Center
4 = Right
5 = Fill
6 = Justify
7 = Center across cells

[Tom Ogilvy]

The type worksheet function shows:

Number
Text
Logical value
Error Value
Array

A date is just a number that represents the elapsed number of days from a
base date. Excel interprets it as a date to make it a date.



--
Regards,
Tom Ogilvy

"Excelente" <[email protected]> wrote
in message news:[email protected]...
>
> Below is the information out of the help for the GET.CELL() function but
> I'm looking for the equivalent test in VBA for the type_num = 4
> situation. I see that #4 below is "4 Same as TYPE(reference)." But
> what does this mean? VBA has test for DataType, but thats good for
> PivotTables. I want to test a cell when it has general formatting to
> know if the data in the cell is a "date", or number, or string, or
> whatever. What are all of the datatypes that a cell can have when
> formatted in the General Format?
>
> Any thoughts?
>
> ================
> Here's the first part out of the help. The same help file has NOTHING
> in it for TYPE(reference). ugh!
> ================
>
> Macro Sheets Only
> Returns information about the formatting, location, or contents of a
> cell.
> Use GET.CELL in a macro whose behaviour is determined by the status of
> a
> particular cell.
>
> Syntax
>
> GET.CELL(type_num, reference)
> Type_num is a number that specifies what type of cell information you
> want. The following list shows the possible values of type_num and the
> corresponding results.
>
> Type_num Returns
>
> 1 Absolute reference of the upper-left cell in reference, as text in
> the
> current workspace reference style.
> 2 Row number of the top cell in reference.
> 3 Column number of the leftmost cell in reference.
> 4 Same as TYPE(reference).
> 5 Contents of reference.
> 6 Formula in reference, as text, in either A1 or R1C1 style depending
> on the
> workspace setting.
> 7 Number format of the cell, as text (for example, "m/d/yy" or
> "General").
> 8 Number indicating the cell's horizontal alignment:
> 1 = General
> 2 = Left
> 3 = Center
> 4 = Right
> 5 = Fill
> 6 = Justify
> 7 = Center across cells
>
>
> --
> Excelente
> ------------------------------------------------------------------------
> Excelente's Profile:
http://www.excelforum.com/member.php...o&userid=23887
> View this thread: http://www.excelforum.com/showthread...hreadid=375238
>

[Bob Phillips]

If Range("A1").HasFormula Then
MsgBox "formula"
ElseIf IsDate(Range("A1").Value) Then
MsgBox "date"
ElseIf IsNumeric(Range("A1").Value) Then
MsgBox "number"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Excelente" <[email protected]> wrote
in message news:[email protected]...
>
> Below is the information out of the help for the GET.CELL() function but
> I'm looking for the equivalent test in VBA for the type_num = 4
> situation. I see that #4 below is "4 Same as TYPE(reference)." But
> what does this mean? VBA has test for DataType, but thats good for
> PivotTables. I want to test a cell when it has general formatting to
> know if the data in the cell is a "date", or number, or string, or
> whatever. What are all of the datatypes that a cell can have when
> formatted in the General Format?
>
> Any thoughts?
>
> ================
> Here's the first part out of the help. The same help file has NOTHING
> in it for TYPE(reference). ugh!
> ================
>
> Macro Sheets Only
> Returns information about the formatting, location, or contents of a
> cell.
> Use GET.CELL in a macro whose behaviour is determined by the status of
> a
> particular cell.
>
> Syntax
>
> GET.CELL(type_num, reference)
> Type_num is a number that specifies what type of cell information you
> want. The following list shows the possible values of type_num and the
> corresponding results.
>
> Type_num Returns
>
> 1 Absolute reference of the upper-left cell in reference, as text in
> the
> current workspace reference style.
> 2 Row number of the top cell in reference.
> 3 Column number of the leftmost cell in reference.
> 4 Same as TYPE(reference).
> 5 Contents of reference.
> 6 Formula in reference, as text, in either A1 or R1C1 style depending
> on the
> workspace setting.
> 7 Number format of the cell, as text (for example, "m/d/yy" or
> "General").
> 8 Number indicating the cell's horizontal alignment:
> 1 = General
> 2 = Left
> 3 = Center
> 4 = Right
> 5 = Fill
> 6 = Justify
> 7 = Center across cells
>
>
> --
> Excelente
> ------------------------------------------------------------------------
> Excelente's Profile:
http://www.excelforum.com/member.php...o&userid=23887
> View this thread: http://www.excelforum.com/showthread...hreadid=375238
>

[Tom Ogilvy]

Not sure IsDate is going to make it for a cell formatted as General as asked
by the OP:

ActiveCell.Value = DateValue("01/01/2001")
? isdate(activecell.Value)
True
activecell.NumberFormat = "General"
? isdate(activeCell.Value)
False

Of course maybe he only meant started out as being formatted as general;
before the date was entered.
--
Regards,
Tom Ogilvy

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
>
> If Range("A1").HasFormula Then
> MsgBox "formula"
> ElseIf IsDate(Range("A1").Value) Then
> MsgBox "date"
> ElseIf IsNumeric(Range("A1").Value) Then
> MsgBox "number"
> End If
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Excelente" <[email protected]> wrote
> in message news:[email protected]...
> >
> > Below is the information out of the help for the GET.CELL() function but
> > I'm looking for the equivalent test in VBA for the type_num = 4
> > situation. I see that #4 below is "4 Same as TYPE(reference)." But
> > what does this mean? VBA has test for DataType, but thats good for
> > PivotTables. I want to test a cell when it has general formatting to
> > know if the data in the cell is a "date", or number, or string, or
> > whatever. What are all of the datatypes that a cell can have when
> > formatted in the General Format?
> >
> > Any thoughts?
> >
> > ================
> > Here's the first part out of the help. The same help file has NOTHING
> > in it for TYPE(reference). ugh!
> > ================
> >
> > Macro Sheets Only
> > Returns information about the formatting, location, or contents of a
> > cell.
> > Use GET.CELL in a macro whose behaviour is determined by the status of
> > a
> > particular cell.
> >
> > Syntax
> >
> > GET.CELL(type_num, reference)
> > Type_num is a number that specifies what type of cell information you
> > want. The following list shows the possible values of type_num and the
> > corresponding results.
> >
> > Type_num Returns
> >
> > 1 Absolute reference of the upper-left cell in reference, as text in
> > the
> > current workspace reference style.
> > 2 Row number of the top cell in reference.
> > 3 Column number of the leftmost cell in reference.
> > 4 Same as TYPE(reference).
> > 5 Contents of reference.
> > 6 Formula in reference, as text, in either A1 or R1C1 style depending
> > on the
> > workspace setting.
> > 7 Number format of the cell, as text (for example, "m/d/yy" or
> > "General").
> > 8 Number indicating the cell's horizontal alignment:
> > 1 = General
> > 2 = Left
> > 3 = Center
> > 4 = Right
> > 5 = Fill
> > 6 = Justify
> > 7 = Center across cells
> >
> >
> > --
> > Excelente
> > ------------------------------------------------------------------------
> > Excelente's Profile:
> http://www.excelforum.com/member.php...o&userid=23887
> > View this thread:
http://www.excelforum.com/showthread...hreadid=375238
> >
>
>

[Excelente]

Many thanks Tom! This was just what I needed. I appreciate your thoroughness as well.