VBA How to identify if a column's contents is

问题:

I have got a qestion regarding Excel VBA. I have to perform this task automatically. Is there any possibility to check whether an entire column's contents (not talking about a cell format) is numerical (containing only numbers)? Of course, I know that an alternative way is avaible, i.e. going down cell by cell. I know that a set filter can identify whether the column is numerical. How to get this information via VBA?

回答1:

COUNT counts numbers; COUNTA counts numbers and text.

=Count(A:A)=CountA(A:A)

... returns true if column A contains numbers only. In VBA as,

Debug.Print CBool(Application.Count(Columns(1))=Application.CountA(Columns(1)))

To convert text-that-looks-like-numbers to true numbers, use TextToColumns, Fixed width, Finish on the column.

 Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)

回答2:

Probably the simplest solution I can thing of:

Sub TestMe()

    Dim myCell  As Range
    Dim myCol   As Range

    Set myCol = Worksheets(1).Columns(1).Cells.SpecialCells(2)

    For Each myCell In myCol.Cells
        If Not IsNumeric(myCell) Then
            Debug.Print myCell.Address
            Exit For
        End If
    Next myCell

End Sub

As a next step, try writing a boolean function, that takes the worksheet and the column to search for. It can be of use.