determining cell type through vba

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
hello all

I know of the type function in excel, e.g. =type(A1).

How do I get this information in VBA though?

I'm looping through some columns using text to columns to make numbers as text into numbers, and I want to tell the macro to only act on those cells with a cell type of 2, I can not find it in VBA though.

Please help.

Many thanks

Jon

FYI code attached

Code:
Sub Text2Cols()

Dim x As Long
Dim i As Long
Dim rng As Range

x = Sheet6.Range("Data").Columns.Count
    
For i = 1 To x


Debug.Print i
Set rng = Sheet6.Columns(i)
Application.DisplayAlerts = False

' If rng.cells(2)....type =  2 then
    rng.TextToColumns DataType:=xlDelimited
' End if

Set rng = Nothing
Next i

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Have you tried IsNumeric?
 

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
Hi Rory,

Thanks for replying on my thread.

I have tried that yes, unfortunately it seems to give a value of 'True' to cells that are of type 2 if they resemble a number.

Jon
 

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
I should probably highlight that when I say type 2 I mean 'text' as shown on the formula help for type().
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Try:
Code:
If Typename(cell_here) = "String" then
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Out of interest, why do you need to distinguish between them? Does using text to columns on numeric types cause a problem?

It seems to me you can do all of this in one line:
Code:
Sheet6.Range("Data").Value = Sheet6.Range("Data").Value
 

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187

ADVERTISEMENT

Out of interest, why do you need to distinguish between them? Does using text to columns on numeric types cause a problem?

It seems to me you can do all of this in one line:
Code:
Sheet6.Range("Data").Value = Sheet6.Range("Data").Value

It's not essential, I just find it annoying when I don't know how to access information in VBA that I can get easily otherwise.

The code works ok as is, I'd just like to know how to determine cell type in VBA.

My code is doing T2C on columns unneccessarily I suppose on some columns containing formulae to the right of my data block, but it's not the end of the world.

I'm still curious though if anyone happens to know?

I tried Rory's latest suggestion, maybe i'm doing it wrong but with
?typename(activecell) in my Immediate Window I keep getting 'Range'.

I suppose the .Value = Value method is a possiblilty, but my data range has formulae columns so I'd need to involve more named ranges, which I'd rather avoid in truth.

Thanks

Jon
 
Last edited:

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Jon,

It would be
Code:
?typename(activecell.value)
But I think the way I suggested is much more efficient - no looping and text to columns overhead. You just need to adjust the range to the range containing constants which need to be examined. I can't do this because the information I would need isn't on the thread, so I just used Range("Data") in my example. You do not need more named ranges to do this: if you give the specifics I'm sure one of us will be able to come up with something.

Hope that helps...
 

berlinhammer

Board Regular
Joined
Jan 30, 2009
Messages
187
Hi Colin

I'm not sure you saw all of my last post as I edited again.

I do like your idea but my problem is that a lot of spreadsheets we use have varying numbers of rows and columns, but the same basic principal of a bunch of raw data with some calculated columns to the right in order to create some pivots.

The ranges change and this text to columns problem is a feature across several workbooks, of which I don't want to complicate the Named Ranges and related pivots. As things stand I just name the range as 'Data' (including the calculated columns) manually and refresh all the pivots that depend upon that Named Range. I was keen to have a quick macro I could apply to any of them I have open without messing up the calculated columns or pivots.

Thanks for the suggestion, I might just try and do that on my raw data file before I put it into the workbook in future.

Jon
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Jon,
I suppose the .Value = Value method is a possiblilty, but my data range has formulae columns so I'd need to involve more named ranges, which I'd rather avoid in truth.
That's not the case: you don't need to complicate the named ranges for my suggestion. You can use them exactly as they are now.

If the columns within "Data" containing constants or formulas are consistent then you can use an Intersect (or similar) to reference only the cells containing constants.

If the columns within "Data" containing constants or formulas are inconsistent across the workbooks then you can easily reference the cells containing constants by using the Range.SpecialCells() method.

To be more specific I need more specific information on the thread.

Hope that helps...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,576
Messages
5,659,622
Members
418,515
Latest member
tobyvb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top