Defining between a number and a character

[lhernandez]

I am working with a worksheet that list numbers or words through column A.
For ex.
380
Control
1000
2500
500
DC
100
20000

I have written a macro to search through this column and on a new worksheet, if the value is less than 1000, then it is assigned to type A.
If the value is between 1001 and 6600 then it is assigned to type B.
If the value is greater than 6601 then it is assigned to C, and if the cell equals "Control" or "DC" then it is assigned to type D.
But for some reason Control and DC is read in the macro as greater than 6601 and is assigned to type C.
How can I tell the cell to read between numbers and characters or how would I go about fixing this?
Any help would be great
Thank you,

 

[Von Pookie]

Excel always considers text is greater than *any* number. You can use the ISNUMBER and/or the ISTEXT functions to determine if a value is text or numeric.

 

[PolarBear]

I just worked through the same problem. I had numbers that were sometimes of the form "123", and sometimes "123X". I had problems until I read TFM, and realized that IsNumeric takes a Variant argument. After that, it was clear sailing:

Sub SetAccounts()
' This sub looks at the account number in the first row. This can be either a number, or
' a number with a single text character at the end. Hence, it must be defined as a "Variant"
' class of variable. The sub looks at the last character of the Variant. If it is text, it is copied
' into column C, while the number part is copied into column B. If there is no text, the entire
' number is copied into column B, and column C is left blank.
Dim TestAccount As Variant
Dim LastRow As Double
Dim RowCount As Double
Dim strLength As Integer
LastRow = LCD ' LCD is a function that returns the last row with data on the active sheet
For RowCount = 2 To LastRow
    TestAccount = Range("A" & RowCount).Value
    If IsNumeric(Right(TestAccount, 1)) Then
        Range("C" & RowCount).Value = TestAccount
        Range("D" & RowCount).Value = " "
    Else
        Range("C" & RowCount).Value = Left(TestAccount, Len(TestAccount) - 1)
        Range("D" & RowCount).Value = Right(TestAccount, 1)
    End If
Next RowCount
    
End Sub

Hope this helps...

 

[lhernandez]

how do you use the ISNUMERICA or ISTEXT. I have just tried adding it in my program and it says "Sub or Function" not defined?

 

[PolarBear]

First, it's IsNumeric, not IsNumericA. Second, what version of Excel are you running? Maybe this function is not supported on old systems. Otherwise, it's a function that returns True or False, depending on whether the variant can be a number or not.

Examples:
dim TestVal as Variant
TestVal = "45"
IsNumeric(TestVal) returns "True"
TestVal = "45 Colt"
IsNumeric(TestVal) returns "False"