Problem using a large amount of Range cells

[ABabeNChrist]

I’m having 2 problems when using a large amount of range locations within the code
If I were to use let’s say 66% of the range locations needed, it seems to work great, by when I load the full range of needed cells I then receive this unwanted error message.

untitled.JPG

The second part is and I know it is probably very simple is, how do I use “_” to break down this long line of range code. I tried everything I can think of.
Below is a sample workbook

Sample Book.xlsm

[HansV]

The way to break a long string is to concatenate the parts using &. Each of the parts has to be enclosed in quotes.

So for example "Microsoft Excel" can be split into "Microsoft " & "Excel". And if you want to put this on two lines:

"Microsoft " & _
"Excel"

In your code, you'd get

Code: Select all

    If Not Intersect(Range("L14,L19,L26,L35,L42,L49,L55,L61,L66,L76,L82," & _
        "L87,L100,L107,L119,L130,L141,L152,L158,L171,L178,L188,L197,L203," & _
        "L210,L215,L220,L226,L231,L236,L241,L247,L252,L257,L263,L279,L285," & _
        "L292,L299,L306,L313,L318,L323,P14,P19,P26,P35,P42,P49,P55,P61,P66," & _
        "P76,P82,P87,P100,P107,P119,P130,P141,P152,P158,P171,P178,P188," & _
        "P197,P203,P210,P215,P220,P226,P231,P236,P241,P247,P252,P257,P263," & _
        "P279,P285,P292,P299,P306,P313,P318,P323,S14,S19,S26,S35,S42,S49," & _
        "S55,S61,S66,S76,S82,S87,S100,S107,S119,S130,S141,S152,S158,S171," & _
        "S178,S188,S197,S203,S210,S215,S220,S226,S231,S236,S241,S247,S252," & _
        "S257,S263,S279,S285,S292,S299,S306,S313,S318,S323"), Target) Is Nothing Then

But this won't help - the maximum length of the combined string is 255 characters. So you'll have to write several parts, each with a range string of 255 characters or less.

[ABabeNChrist]

Thank you Hans
I understand the part about how to break a long string, that I got, but I’m not sure I fully understand what you mean by;

But this won't help - the maximum length of the combined string is 255 characters. So you'll have to write several parts, each with a range string of 255 characters or less.

I’m still a little confused there.

[HansV]

The total length of the string "L14,L19, ... ,S323" is 608 characters. The expression Range("...") will fail if the string between the quotes is longer than 255 characters.

You could use the following construction instead:

Code: Select all

    Select Case Target.Column
    ' Check for columns L, P or S
    Case 12, 16, 19
        Select Case Target.Row
        ' Check for rows 14, 19, ..., 323
        Case 14, 19, 26, 35, 42, 49, 55, 61, 66, 76, 82, 87, 100, _
        107, 119, 130, 141, 152, 158, 171, 178, 188, 197, 203, _
        210, 215, 220, 226, 231, 236, 241, 247, 252, 257, 263, _
        279, 285, 292, 299, 306, 313, 318, 323
            ' Code to insert a picture goes here
            Cancel = True
            ...
        End Select
    End Select

The two Select Case statements replace the If ... Then line, and the two End Select statements replace the corresponding End If.

[ABabeNChrist]

Thank you Hans
That’s Awesome, you are the Jeti Master
And thank you for such a great explanation, very much greatly appreciated.

[ABabeNChrist]

I made all the correction and of course everything worked PERFECT

[HansV]

Great! Good to hear that.

[ABabeNChrist]

Hi Hans
Everything is working great with previous post, but now I have another situation similar, except I have mutable random cells, I’m not sure of the exact number of characters, but if I were to guess it would probably be close to 1,000. The code you provided earlier in this thread will not work because there is no sequence of number for me to add …To…
I was wondering is it possible to use the code you provided for the first 255 characters and then repeat this code for each column, something like this

Code: Select all

    Select Case Target.Column
        ' Check for columns A
    Case 1
        Select Case Target.Row
            ' Check for rows 1
        Case 14, 19, 26, 35, 42, 49, 55, 61, 66, 76, 82, 87, 100, _
             107, 119, 130, 141, 152, 158, 171, 178, 188, 197, 203
            ' Code to insert a picture goes here
            Cancel = True

        End Select
    End Select

    Select Case Target.Column
        ' Check for columns L
    Case 12
        Select Case Target.Row
            ' Check for rows 14
        Case 210, 215, 220, 226, 231, 236, 241, 247, 252, 257, 263, _
             279, 285, 292, 299, 306, 313, 318, 323
            ' Code to insert a picture goes here
            Cancel = True

        End Select
    End Select

    ' and so on for the remaining 4

would this be the correct way to do this

[HansV]

I'm afraid I don't understand your question. Why can't you use the type of code outlined in Post=17027 higher up in this thread?

[ABabeNChrist]

In using the code you provided higher up in this thread, if I were to enter all the Target.Column in same line of code with all the Target.Row the characters size would be too large, beyond 255 characters, plus every column / row will be affected. See if I were to use column A, B and C as my Target.Column and 1,3,5 as my Target.Row
Since my cell locations were A1, B3, C5, it would then also affect A, B and C with each row #
I believe that is correct

[HansV]

The 255 character limit applied when you used Range("A1,B3,C5, ..."), with one long string specifying the cells. It does not apply when you use the code I posted later on.

If the cells are not distributed regularly across some columns, try something like this:

Code: Select all

    Select Case Target.Address(False, False)
    ' Check some individual cells
    Case "A1", "B3", "C5"
        Cancel = True
        ...
    End Select

[ABabeNChrist]

Thank you Hans
For another great piece of information, now I need to download this info to my brains hard drive.

[ABabeNChrist]

First off thank you Hans
It worked great
I have a question though?
What is the correct method of organizing a long target string, should I go by using columns in order first “A1”, “A13”, “A18” or row numbers like “A1”, “B2”, “C4”, “A5”

Code: Select all

    Select Case Target.Address(False, False)
    ' Check some individual cells
    Case "A1", "B3", "C5"
        Cancel = True
        ...
    End Select

[HansV]

Since you are *not* using a long string, but many separate short strings, its doesn't matter. The code will work regardless of the order in which you specify the cells.

Excel will check the cells in the order they are listed, so if you expect some cells to be double-clicked most often, list those first for optimum performance.

[ABabeNChrist]

Very cool thank you

[ABabeNChrist]

Hi Hans
I tried using this newer piece of code you provided with a Worksheet_BeforeRightClick
That I use to populated a name list. It will populate the list OK, but it seems to run the code in all cells, but of course only populates data validation name list.

Code: Select all

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
' Appends data entry to dynamic validation range on right click
    Select Case Target.Address(False, False)
        ' Check some individual cells
    Case "M52", "M55", "M244", "M266", "M275", "M288", "N12", "N17", "N23", "N24", _
         "N32", "N33", "N40", "N47", "N53", "N59", "N64", "N69", "N74", "N79", "N80", _
         "N85", "N98", "N105", "N112", "N113", "N114", "N117", "N123", "N124", "N125", _
         "N128", "N134", "N139", "N145", "N150", "N156", "N176", "N183", "N186", "N192", _
         "N195", "N200", "N201", "N207", "N208", "N213", "N218", "N224", "N229", "N234", _
         "N239", "N245", "N250", "N255", "N261", "N269", "N273", "N276", "N277", "N283", _
         "N289", "N290", "N297", "N304", "N311", "N316", "N321", "O11", "O31", "O38", "O39", _
         "O45", "O46", "O70", "O71", "O73", "O97", "O103", "O155", "O163", "O167", "O169", _
         "O174", "O175", "O182", "O184", "O185", "O191", "O193", "O194", "O223", "O267", _
         "O271", "O272", "O274", "O295", "O295", "O302", "O309", "P22", "P91", "P93", _
         "P94", "P95", "P96", "P162", "P168", "P270", "P303", "P310", "Q72", "Q104", _
         "Q111", "Q122", "Q133", "Q144", "Q164", "Q165", "Q181", "Q190", "Q268"

        Cancel = True

    End Select
    ' Your code here; Target is the cell being double-clicked
    Dim inter As Range    ' a cell with validation, maybe
    Dim cell As Range
    Dim r As Range    ' validation range
    Dim sVal As String    ' list validation formula
    Dim Answer As String
    Dim MyNote As String


    'If Target.Count > 1 Then Exit Sub
    'Place your text here
    MyNote = "Select Yes to add new comment to list, or select No to cancel selection."

    'Display MessageBox
    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "HomInspect")

    If Answer = vbNo Then
        'Code for No button Press
        MsgBox "You have selected No, your selection has now been cancelled."
        Exit Sub
    Else
        'Code for Yes button Press
        MsgBox "Your new comment has now been added to list."
    End If

    On Error Resume Next
    Set inter = Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation))
    If inter Is Nothing Then Exit Sub
    For Each cell In inter
        If cell.Validation.Type <> xlValidateList Then Exit Sub

        sVal = cell.Validation.Formula1
        If Left(sVal, 1) <> "=" Then Exit Sub

        Set r = ThisWorkbook.Names(Mid(sVal, 2)).RefersToRange
        If r Is Nothing Then Exit Sub
        If IsNumeric(Application.Match(cell.Text, r, 0)) Then Exit Sub

        Cancel = True
        With r
            .Parent.Cells(Me.Rows.Count, .Column).End(xlUp)(2).Value = cell.Text
            .Resize(.Count + 1).Sort _
                    Key1:=r(1), Order1:=xlAscending, _
                    MatchCase:=False, Orientation:=xlTopToBottom, Header:=xlNo
        End With
    Next cell
    Beep    ' the sound of success
End Sub

[HansV]

You should obviously put your code above the line End Select, not below it!

[ABabeNChrist]

Hi Hans
I tried that yesterday also, I put End Select just before End Sub, nothing seemed to happen, no error or anything

[HansV]

What are you trying to accomplish here? The original code (without Select Case) already acts on the cells with validation only. So why are you explicitly checking the cell addresses?

[ABabeNChrist]

I thought it might be a little confusing when a message request pops up the the screen asking
"Select Yes to add new comment to list, or select No to cancel selection."
only of course when a right click is made.and this happens in all cells
In the code I posted in 17289, as I mentioned it worked, but will not work when i put End Select just above End Sub
what I'm hoping to accomplish is have the code run only run in selected cells