Defining between a number and a character

lhernandez

Active Member
Joined
May 22, 2006
Messages
282
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,
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Sep 26, 2005
Messages
215
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:
Code:
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

Active Member
Joined
May 22, 2006
Messages
282
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

Board Regular
Joined
Sep 26, 2005
Messages
215
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"
 

Forum statistics

Threads
1,136,345
Messages
5,675,225
Members
419,555
Latest member
Paddington

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