Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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 --------------- Jacob Skaria "D. Stacy" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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" wrote: 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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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. D. Stacy wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error code 91
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 "Dave Peterson" wrote: 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. D. Stacy wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
OnTime code error "can't execute code in break mode" | Excel Programming | |||
Error in Excel VBA Code (Error 91) | Excel Programming | |||
How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming |