Selecting non sequenced cells in a range

[loudstil]

Hi,
I'm trying to use the SUM function with non sequenced cell but the Range or any other function won't allow me to activate the selecting cells if there are more then 41 cell, I wrote somthing like so:

Sub Test()
Dim str AS String
Dim R AS Range

str = "A1,A3 ...... ,A57" 'this is only an example, assum that there are _
                                 more then 41 cells
Set R = Range(str) 'Here is the Problem, I get a Runtime error 1004 _
                          Application-defined or object-defined error
R.select
End Sub

But if the str input was a 41 or less Cells address string then it works

any help will be appreciated

Daniel

 

[Jon von der Heyden]

Hi Daniel and welcome to the board.

You might as well populate the range address rather than use a string variable:

Set R = Range("A1,A3,...A57")

 

[loudstil]

Hi Jon
Thanks for the Reply.
I'm using the String because I wrote a Macro that needs to use the Sum function only to a certain cells and its more then 100 cells so in the macro I'm allways keeping those certain cells in an array and then take their address and put then in a string.
I havn't thought about another way of doing it and I'm open to another suggestions

Thanks, Daniel

 

[Jon von der Heyden]

Hi Daniel

In theory what you are doing should work. I have even tested with your sample and provided each element is a valid cell address then it works.

But perhaps you can share the actual code that you are attempting to use? How do you determine which cells make up the wanted range?

BTW - rather than storing addresses in a string we can union ranges to a defined range object.

E.g:

Dim rngTable as Range
 
Set rngTable = Range("A1")
Set rngTable = Union(rngTable,Range("A3"))

 

[DonkeyOte]

If there is some logic that allows you to establish which cells are to be summed then it would make sense to utilise that also (ie via a SUMIF or SUMPRODUCT).

If for ex. you're summing every other row but all rows are numeric (ie SUM won't work) then you could use the contiguous range in a SUMPRODUCT but include based on Odd status of Row (via MOD)

 

[loudstil]

Hi Jon
I tried your Idea but it still do not work for a range larger than 43 cells, after a the 43 cell it won't add him to the range...

 

[loudstil]

Well I kind of figure it out, the Range Method can only get a String up to 255 characters and my string is way larger then that

 

[Jon von der Heyden]

Hi again Daniel

I really think you need to share your code and explain more clearly what you are attempting to do. That is, how do you determine which cells should make up the range.

Note DO's comment earlier, there are means for calculating only certain cells in a given range...

It seems that when you are using a string of range addresses you are confined to 42. You are confined to 42 because of the legnth of the string (255). But your union range can cope with many, many more ranges.

Take the following example:

[COLOR=blue]Sub[/COLOR] test()
 
[COLOR=blue]Dim[/COLOR] rngCell [COLOR=blue]As[/COLOR] Range
[COLOR=blue]Dim[/COLOR] lng [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]Dim[/COLOR] rngUnion [COLOR=blue]As[/COLOR] Range
 
[COLOR=blue]For[/COLOR] [COLOR=blue]Each[/COLOR] rngCell [COLOR=blue]In[/COLOR] Selection
    lng = lng + 1
    [COLOR=blue]If[/COLOR] lng [COLOR=blue]Mod[/COLOR] 2 = 0 [COLOR=blue]Then[/COLOR]
        [COLOR=blue]If[/COLOR] rngUnion [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR]
            [COLOR=blue]Set[/COLOR] rngUnion = rngCell
        [COLOR=blue]Else[/COLOR]
            [COLOR=blue]Set[/COLOR] rngUnion = Union(rngUnion, rngCell)
        [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
[COLOR=blue]Next[/COLOR] rngCell
 
[COLOR=blue]Debug.Print[/COLOR] rngUnion.Count
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

Here we dealing with a range object, not a string. Select a single column selection of e.g. 200 rows and run this macro, and it will tell you how many cells the range object is comprised of.

We can use this range in a calculation, e.g.:

Debug.Print Application.Sum(rngUnion)

So I suppose the point here is that whatever the logic is that you are using to determine which ranges apply, rather than storing the address to a string, maintain a range object and union each cell to that range object instead.

 

[loudstil]

Hi Jon
I used your idea above and It worked.
What I did was so, I saved all the addresses of the cells I counted in a string and then called to a function which get the string length and dividing it by 254, the max characters in a SUM formula, then I saved every substring in a strings array and I used the SUM forrmula for each array member and then I used the SUM formula for the whole Range of the Sums, Here is the Function:

Function GetStrings(ByRef s As String)
'Resolving the SUM formula limit issue
Dim i, j, length, Divider As Integer
Dim t(), s1 As String
Divider = 254
s1 = s
length = Len(s)
i = Round(length / Divider)
ReDim t(i + 1)
For j = 1 To i + 1
   If j = i + 1 Then
       t(j) = s1
       GoTo Con
   Else
    t(j) = Left(s1, Divider)
   End If
   Do While Not ChckString(t(j))
        Divider = Divider - 1
        t(j) = Left(s1, Divider)
        If ChckString(t(j)) Then GoTo Con
   Loop
Con:
      length = length - Divider
      If j <= i Then
        s1 = Right(s1, length)
        t(j) = Left(t(j), Len(t(j)) - 1)
      End If
      Cells(j, 4).Value = t(j)
      Cells(j, 2).Formula = "=Sum(" & t(j) & ")"
Next j
Cells(12, 2).Formula = "=Sum(" & Range(Cells(1, 2), Cells(11, 2)).Address & ")"
End Function

This Function help to explode the string excetlly in the ',' character

Function ChckString(ByVal Cs As String) As Boolean
'Checking
If Right(Cs, 1) <> "," Then
    ChckString = False
Else
    ChckString = True
End If
End Function

 

[Jon von der Heyden]

Hi Daniel

What is the routine that builds the string? I still don't see any point in loading a string at all, and instead I would use sub routine / function to build a range object.