Range method error

[KenKen]

Hi folks,

I'm trying to select a number of cells to format using the Range method but it seems that when the selection string becomes too long, the method fails.

This is some test code that illustrates the problem, Test 1 - all values work, whereas test 2 fails:

Sub xval_mr_excel()
Dim TEST_1a As String
TEST_1a = "$D$13"
Dim TEST_1b As String
TEST_1b = "$D$45,$D$48,$D$49,$D$52,$D$53,$D$54,$D$55,$D$56,$D$57,$D$58,$D$59,$D$60,$D$61,$D$62"
Dim TEST_1c As String
'limit of acceptable range parameter
TEST_1c =
"$D$13,$D$24,$D$25,$D$36,$D$67,$D$98,$D$20,$D$99,$D$21,$D$22,$D$23,$D$24,$D$25,$D$26,$D$27,$D$28,$D$29,$D$30,$D$31,$D$32,$D$33,$D$34,$D$35,$D$36,$D$37,$D$38,$D$39,$D$40,$D$41,$D$42,$D$43,$D$44,$D$45,$D$46,$D$47,$D$48,$D$49,$D$50,$D$51,$D$52,$D$53"

'Remove protection for this programmatic format edit
 ActiveSheet.Protect AllowFormattingCells:=True, Password:=passwd
 Range(TEST_1a).Font.ColorIndex = 5
 Range(TEST_1b).Font.ColorIndex = 5
 Range(TEST_1c).Font.ColorIndex = 5
'Reinstate protection after this programmatic format edit has been made.
 ActiveSheet.Protect AllowFormattingCells:=False, Password:=passwd

'beyond limit of parameter passed to range - this fails.
Dim TEST2 As String
TEST2 = "$D$13,$D$24,$D$25,$D$36,$D$67,$D$98,$D$20,$D$99,$D$21,$D$22,$D$23,$D$24,$D$25,$D$26,$D$27,$D$28,$D$29,$D$30,$D$31,$D$32,$D$33,$D$34,$D$35,$D$36,$D$37,$D$38,$D$39,$D$40,$D$41,$D$42,$D$43,$D$44,$D$45,$D$46,$D$47,$D$48,$D$49,$D$50,$D$51,$D$52,$D$53,$D$54,$D$55"
'Remove protection for this programmatic format edit
 ActiveSheet.Protect AllowFormattingCells:=True, Password:=passwd
 Range(TEST_2).Font.ColorIndex = 5
'Reinstate protection after this programmatic format edit has been made.
 ActiveSheet.Protect AllowFormattingCells:=False, Password:=passwd
End Sub

Other things to note:
The strings will not be sequences of numbers that could be replaced by ($D$14:$D$55)

When I put a watch on the TEST_2 string the last few characters are shown as:

"...,$D$52,$D$53,$D$5,"

instead of what I would have expected which is:
"...$D$52,$D$53,$D$54,$D$55"

However if I print the string variable to the immediate window it appears as I'd expect it to. I don't know enough about the VB editor to know whether that last point is just a distraction or not, but if it's not related I think it's a strange co-incidence that the point that the Range method fails is the same point that the VBE watch does too.

Note that all of these cells exist and are editable which is shown by TEST_1b and TEST_1a.

All help and suggestions of how to get the Range method working properly would be appreciated

Thanks,

Ken.

Excel 2002 SP3, Windows XP

 

[Jonmo1]

While I won't claim to know WHY it fails, however,

TEST2 = "$D$13,$D$14,$D$15,$D$16,$D$17,$D$18,$D$19,$D$20,$D$21,$D$22,$D$23,$D$24,$D$25,$D$26,$D$27,$D$28,$D$29,$D$30,$D$31,$D$32,$D$33,$D$34,$D$35,$D$36,$D$37,$D$38,$D$39,$D$40,$D$41,$D$42,$D$43,$D$44,$D$45,$D$46,$D$47,$D$48,$D$49,$D$50,$D$51,$D$52,$D$53,$D$54,$D$55"

Can and definately SHOULD be changed to

TEST2 = "$D$13:$D$55"

 

[Richard Schollar]

Hi Ken

Why don't you pass the ranges in like:

TEST_1c =
"D13:D53"

 

[KenKen]

Update

Hi,
I knew that there was something that I forgot to mention in the original post... The strings that I showed in the test aren't very representative of what I'll actually be using. In fact there won't necessarily be sequences of cells that I want to change at one time - i.e. "$D$43,$D$52,$D$54,$D$55,$D$59" would be a more accurate representation of the type of data that will be used in practise. I've updated the original post so that it will be clearer to anyone seeing it for the first time.

Thanks for your responses so far though.

Ken.

 

[Jonmo1]

maybe try using an array and loop...
for example

myarray = array("$D$43","$D$52","$D$54","$D$55","$D$59")

for i = lbound(myarray) to ubound(myarray)
    range(myarray(i)).Font.ColorIndex = 5
next i

 

[Norie]

Ken

Can you post something that is representative?

And also explain the logic/criteria behind which cells to work with?

 

[KenKen]

Logic behind range usage

Hi,
I'm actually maintaining this code which was originally written some time ago.

The logic is that the values in the spreadsheet are checked to make sure that they fulfil various criteria and any that don't are highlighted to the user by having their color changed so that it's clear which cells the error message is referring to. At the moment when a user enters too many cells with problems the system just falls over, which isn't quite what we planned!

I guess that no one knows of any reason why Range should behave like that?

Jonmo - thanks for your suggestion, I think it's a neat solution unless anyone else can come up with a way of getting the function to work as documented.

Back tomorrow...

Ken.

 

[Norie]

Ken

I think we realised what the code is meant to do - we can read the code and interpret it.

But what are/is the criteria? There's none in the code.

PS And do you really need code anyway?

 

[Richard Schollar]

Ken

In the same vein as Norie's questions, how do you determine which cells you need to work with?

 

[KenKen]

Hi,
Just to follow up on this, I took Jonmo1's suggestion of using an array and that solved my problem. For reference here are selected bits of the code implementing it (not exactly rocket science, but it might be helpful to someone one day) :

Declarations

Public k As Integer
Public test_loq_array() As String
Public test_loq_array_holds_data As Boolean


Populate array

      If test_data < max_lloq Or test_data > min_uloq Then
        'resize array and add the current cell as a new element.
        If test_loq_array_usable Then
            ReDim Preserve test_loq_array(UBound(test_loq_array) + 1)   ' Increase array size by 1
        Else
            ReDim Preserve test_loq_array(1)   ' Make array usable.
            test_loq_array_usable = True
        End If
        test_loq_array(UBound(test_loq_array)) = Cells(i, 4).Address
        ok_test_loq = False
      End If


Use values in array

  For k = LBound(test_loq_array) To UBound(test_loq_array)
      Range(test_loq_array(k)).Font.ColorIndex = 7
  Next k

In answer to Norie and Richard's questions about how the cells are decided, I hope you don't mind me not posting more of the code - the sections I've pulled out are scattered across quite a few modules. The criteria are kind of shown above - testing whether the value in the cell is within certain limits. As I said, I'm maintaining this code and I don't intend to re-engineer it, even if where I am now is not the most sensible way of doing things and I'm very happy that it now works!

Anyway, thank you all for your time.

Ken.