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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,373
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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
35,373
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS

ADVERTISEMENT

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

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
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, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
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, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
365, 2016
Platform
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,132
Messages
5,509,344
Members
408,727
Latest member
Cantello

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top