CODEWITHSUNDEEP
vbaexcel
user3733492
user3733492

Reputation: 9

Issue with VBA Excel number formatting

I have a column of about 450,000 9-digit numbers that are being used to determine the output of another column. Why is the process recognizing the nine digit numbers that start with the number 3 but not 0?

Sub Anything()

Dim rng As Range, cl As Range

Set rng = Range("Y2:Y" & Range("Y" & Rows.Count).End(xlUp).Row)

For Each cl In rng.Cells

    Dim outCell1 As Range
    Set outCell1 = cl.Offset(0, 54)


     Select Case Trim(cl.Value)

        Case "001019147"
            outCell1.Value = "thing1"

        Case "311019147"
            outCell1.Value = "thing2"

All the cells in the column contain 9-digit numbers and although there are about 450,000 cells in the column only about 2000 are processed. Any input?

Upvotes: 0

Views: 139

Answers (2)

SeanC
SeanC

Reputation: 15923

For your select case statement, try using

Select Case Val(Trim(cl.Value))

    Case 1019147
        outCell1.Value = "thing1"

    Case 311019147
        outCell1.Value = "thing2"

This will allow you to have values and/or multiple values.
e.g.

Case 1 to 22
Case 1,2,3,57,999
Case 1 to 8,300,400 to 499

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Select Case Trim(cl.Text)

Upvotes: 0

Related Questions