Error code 91

[D. Stacy]

The followng block of code is producing Error Code 91 while dealing with the
variable "Length".

This code is ran against a verticle colum of text data that is either 1, 2,
3 or 4 characters in length and is numbers stored as text. The range
selected is typically the entire column. My ultimate goal here is to add the
necassary code that will add the appropriate number of leading zeros so that
all the data is 4 characters long.





Sub ConfigureTimeData()
' Counts Characters in Text String then runs code depending on Character
Count
Dim FormulaCells As Range, ConstantCells As Range
Dim Cell As Range
Dim Length As Double
Length = Cell.Characters.Count
If TypeName(Selection) <> "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlTextValues)

On Error GoTo 0

' Process the formula cells
If Not FormulaCells Is Nothing Then
For Each Cell In FormulaCells
If Cell.Value < 2 Then
Cell.Interior.Color = RGB(0, 0, 0)

Else
Cell.Interior.Color = RGB(122, 100, 0)
End If
Next Cell
End If

' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Length
Case 0
Exit Sub
Case 1
Cell.Interior.Color = RGB(255, 0, 0)
Case 2
Cell.Interior.Color = RGB(0, 255, 0)
Case 3
Cell.Interior.Color = RGB(0, 0, 255)
Case 4
Cell.Interior.Color = RGB(50, 0, 0)
Case Is > 5
Cell.Interior.Color = RGB(255, 0, 255)
End Select



Next Cell
End If
End Sub

 

[Jacob Skaria]

What is the range?

You can use the len function to return the number of characters. For example
Length = Len(Cells(1, 1))
will return the number of characters in cell A1

If this post helps click Yes

 

[Dave Peterson]

Are you looking at the length in each cell in that loop?

If yes, then:
delete this line:
Length = Cell.Characters.Count

and change this:
For Each Cell In ConstantCells
Select Case Length
to
For Each Cell In ConstantCells
length = cell.characters.count
Select Case Length

Personally, I'd drop the Length variable completely and use:

For Each Cell In ConstantCells
Select Case len(cell.value)

 

[D. Stacy]

Dave, I took your advice and used the following (shorter) construct:

Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Value)

Case 0
Exit Sub
Case 1
Cell.Interior.Color = RGB(255, 0, 0)
Case 2
Cell.Interior.Color = RGB(0, 255, 0)
Case 3
Cell.Interior.Color = RGB(0, 0, 255)
Case 4
Cell.Interior.Color = RGB(50, 0, 0)
Case Is > 5
Cell.Interior.Color = RGB(255, 0, 255)
End Select



Next Cell
End If

The code is not making it to the select case part of the code, it seems to
be hitting the "If Not ConstantCells Is Nothing Then" going to the end if.
I'm running on the following type of data (formatted as text).

1015
1150
940
730
1200
800
1115
915
1200

Any thoughts?

 

[Dave Peterson]

That means that this line:

Set ConstantCells = Selection.SpecialCells(xlConstants, xlTextValues)

is not returning what you hoped.

Maybe your selection is wrong and doesn't contain any text????

 

[D. Stacy]

I made sure that the data is formatted as text; no improvement.

I changed some of the data entries to letters; it works great on the letters.

I changed the format to "general" and it works on the letters but not on the
numbers.

It seems to work on letters regardless of the selected format and not on
numbers.


? Could it be that the len function only recognizes letters?

 

[Dave Peterson]

Formatting the cells as text isn't enough to change the values to text. You'd
have to do more to convert the values to actual text.

But I'm not sure what you're doing.

Since you're distinguishing between formula cells and constant cells, maybe just
using:

Set ConstantCells = Selection.SpecialCells(xlConstants)
(this includes both numbers and text cells)

Then I'd use:

For Each Cell In ConstantCells
Select Case len(cell.Text)

Using .text instead of .value means that you'll be looking at what's displayed
in a cell--not the value of the cell.

I made sure that the data is formatted as text; no improvement.

I changed some of the data entries to letters; it works great on the letters.

I changed the format to "general" and it works on the letters but not on the
numbers.

It seems to work on letters regardless of the selected format and not on
numbers.

? Could it be that the len function only recognizes letters?

 

[D. Stacy]

Thanks Dave!

This is now working as planned. I'm probably going to post a new thread to
get some help on killing the "change color" stuff and inserting code to
actually perform operations on the data.

Thanks Again