Defining between a number and a character

lhernandez

Active Member
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

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...

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?

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"

Replies
7
Views
243
Replies
0
Views
106
Replies
3
Views
281
Replies
8
Views
514
Replies
1
Views
156

1,217,750
Messages
6,138,407
Members
450,134
Latest member
TYoung24

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.

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

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