Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
Hi
In VBA I want to get the cell information on a cell e.g. in the Application itself, I can put: =CELL("type",A1) in Cell B1 However, I would like to extract the same information about (multiple) cells directly from VBA itself i.e. in a procedure, without entering a formula into an Excel cell. Please can you give me the VBA syntax for that. Many thanks Tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
Hi
In VBA I want to get the cell information on a cell e.g. in the Application itself, I can put: =CELL("type",A1) in Cell B1 However, I would like to extract the same information about (multiple) cells directly from VBA itself i.e. in a procedure, without entering a formula into an Excel cell. Please can you give me the VBA syntax for that. Many thanks Tim You can dupe that function in VBA as follows... Dim c For each c in Range("A1:A5") c.Offset(0, 1) = c.NumberFormat Next 'c -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
"Tim Childs" wrote in message
Hi In VBA I want to get the cell information on a cell e.g. in the Application itself, I can put: =CELL("type",A1) in Cell B1 However, I would like to extract the same information about (multiple) cells directly from VBA itself i.e. in a procedure, without entering a formula into an Excel cell. Please can you give me the VBA syntax for that. CELL + "type" doesn't tell you much - the cell contains a string, is empty, or anything else. Other VBA functions can tell you a lot more but give some idea of what you're looking for and what you want to do with it. Peter T |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
On 03-Mar-17 8:00 PM, Peter T wrote:
"Tim Childs" wrote in message Hi In VBA I want to get the cell information on a cell e.g. in the Application itself, I can put: =CELL("type",A1) in Cell B1 However, I would like to extract the same information about (multiple) cells directly from VBA itself i.e. in a procedure, without entering a formula into an Excel cell. Please can you give me the VBA syntax for that. CELL + "type" doesn't tell you much - the cell contains a string, is empty, or anything else. Other VBA functions can tell you a lot more but give some idea of what you're looking for and what you want to do with it. Peter T Thanks for response I want to distinguish between cells that have been output as labels and those that are values. In the latter, dates are numbers and in the former, I will use TextToColumns to convert the "date labels" to proper date values. I hope that clarifies my request, if not, please do say so. Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
On 03-Mar-17 6:55 AM, GS wrote:
Dim c For each c in Range("A1:A5") c.Offset(0, 1) = c.NumberFormat Next 'c thanks although it is a characteristic of the cell that I want i.e. if it is a label or value etc rather than the number format Tim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
"Tim Childs" wrote in message ... On 03-Mar-17 8:00 PM, Peter T wrote: "Tim Childs" wrote in message Hi In VBA I want to get the cell information on a cell e.g. in the Application itself, I can put: =CELL("type",A1) in Cell B1 However, I would like to extract the same information about (multiple) cells directly from VBA itself i.e. in a procedure, without entering a formula into an Excel cell. Please can you give me the VBA syntax for that. CELL + "type" doesn't tell you much - the cell contains a string, is empty, or anything else. Other VBA functions can tell you a lot more but give some idea of what you're looking for and what you want to do with it. Peter T Thanks for response I want to distinguish between cells that have been output as labels and those that are values. In the latter, dates are numbers and in the former, I will use TextToColumns to convert the "date labels" to proper date values. I hope that clarifies my request, if not, please do say so. First thing to try is simply see if the string dates will coerce to date values, if any 'numbers' return format as date. =(A1)*1 and look for #VALUE! errors If that doesn't fix them try this UDF Function DataType(cel As Range) Dim s As String Select Case VarType(cel) Case vbBoolean: s = "Boolean" Case vbDate: s = "Date" Case vbDouble: s = "Double" Case vbEmpty: s = "Empty" Case vbString: s = "String" Case vbError: s = "Error" Case Else: s = "other" End Select DataType = s End Function =DataType(A1) You might also try F5, Special, Constants and//or Formulas and tick only the Text Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
hi Tim,
For more information about VarType Function like Peter to use, https://msdn.microsoft.com/en-us/lib.../gg278470.aspx Value Constant Description 0 vbEmpty Empty (uninitialized) 1 vbNull Null (no valid data) 2 vbInteger Integer 3 vbLong Long integer 4 vbSingle Single-precision floating-point number 5 vbDouble Double-precision floating-point number 6 vbCurrency Currency value 7 vbDate Date value 8 vbString String 9 vbObject Object 10 vbError Error value 11 vbBoolean Boolean value 12 vbVariant Variant (used only with arrays of variants) 13 vbDataObject A data access object 14 vbDecimal Decimal value 17 vbByte Byte value 20 vbLongLong LongLong integer (Valid on 64-bit platforms only.) 36 vbUserDefinedType Variants that contain user-defined types 8192 vbArray Array isabelle Le 2017-03-04 à 09:29, Peter T a écrit : If that doesn't fix them try this UDF Function DataType(cel As Range) Dim s As String Select Case VarType(cel) Case vbBoolean: s = "Boolean" Case vbDate: s = "Date" Case vbDouble: s = "Double" Case vbEmpty: s = "Empty" Case vbString: s = "String" Case vbError: s = "Error" Case Else: s = "other" End Select DataType = s End Function =DataType(A1) You might also try F5, Special, Constants and//or Formulas and tick only the Text Peter T |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
On 05-Mar-17 6:36 AM, isabelle wrote:
hi Tim, For more information about VarType Function like Peter to use, https://msdn.microsoft.com/en-us/lib.../gg278470.aspx Value Constant Description 0 vbEmpty Empty (uninitialized) 1 vbNull Null (no valid data) 2 vbInteger Integer 3 vbLong Long integer 4 vbSingle Single-precision floating-point number 5 vbDouble Double-precision floating-point number 6 vbCurrency Currency value 7 vbDate Date value 8 vbString String 9 vbObject Object 10 vbError Error value 11 vbBoolean Boolean value 12 vbVariant Variant (used only with arrays of variants) 13 vbDataObject A data access object 14 vbDecimal Decimal value 17 vbByte Byte value 20 vbLongLong LongLong integer (Valid on 64-bit platforms only.) 36 vbUserDefinedType Variants that contain user-defined types 8192 vbArray Array isabelle Le 2017-03-04 à 09:29, Peter T a écrit : If that doesn't fix them try this UDF Function DataType(cel As Range) Dim s As String Select Case VarType(cel) Case vbBoolean: s = "Boolean" Case vbDate: s = "Date" Case vbDouble: s = "Double" Case vbEmpty: s = "Empty" Case vbString: s = "String" Case vbError: s = "Error" Case Else: s = "other" End Select DataType = s End Function =DataType(A1) You might also try F5, Special, Constants and//or Formulas and tick only the Text Peter T Hi Peter and Isabelle Many thanks for the two posts which I will try out Best wishes, Tim |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Syntax for Cell type
"isabelle" wrote in message
hi Tim, For more information about VarType Function like Peter to use, I only inlcuded the 'vartypes' relevant for cells :) Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax for AutoCad File Type *.dwg | Excel Programming | |||
cell reference syntax | Excel Worksheet Functions | |||
VBA Import Convert Data Type from Type 1 to Type 2 | Excel Programming | |||
cell value syntax | Excel Programming | |||
Worksheet().Range(Cell(), Cell()) syntax? | Excel Programming |