Sorting cells by colour and putting their address into an Array-CodePudding

I am new to VBA. So usually I have to research stuff for my codes in order to make them work. Now I am working on a code that has to get the values from cells with background colour that are different from -4142, and then putting those values in an array so that later I can insert those values in a dropdown list. I was testing getting the values of the cells with different colours and putting them into arrays with the code I found in the answer of this question: Appending a dynamic array in VBA but for some reason, when I run the code I get the error 13 incompatible types (I don't know if the error message is that in english because my vba is in another language, but the error number is 13 non the less), in the line myArray(X) = cell.Address

It may be very silly but I dont know what to do.

Sub SelectByColor()
Dim cell As Range
Dim rng As Range
Dim LR As Long
Dim myArray() As Double, X As Long
X = 0
'For understanding LR = Last Row
LR = Range("B:B").SpecialCells(xlCellTypeLastCell).Row

ReDim Preserve myArray(X)
Set rng = Range("B2:B" & LR)
For Each cell In rng.Cells
    If cell.Interior.ColorIndex <> -4142 Then
        myArray(X) = cell.Address
        X = X   1
        If X < N Then ReDim Preserve myArray(0 To X)
        mystr = mystr & cell.Address & ","
    End If
Next cell
mystr = Left(mystr, Len(mystr) - 1)
MsgBox mystr
MsgBox myArray(X)

End Sub

The mystr part is to see if the code would be getting the correct values, and it is, but it is not appending in the array.

CodePudding user response：

(a) You get your runtime error because you declares your array to hold numbers, but you are trying to write addresses (=strings) into it. An cell address (eg $A$1) cannot be converted into a number and therefore VBA throws that error 13.

(b) A list of values used as Data Validation can be created by a range of cells or by a (hardcoded) list of values. However, the range need to be contiguous, which is not the case for your requirements.

So what you need is a list of values. The values need to be separated by ",". You can do the by creating an array as you do in your code and then use the Join-function. However, the array needs to be of type String or Variant, it will not work with Double.

As I don't like to use Redim Preserve in a Loop (very inefficient), I changed the logic by sizing the array with the maximum of possible values (LR) and then use only a single Redim to remove unused entries after the values are filled.

ReDim myArray(LR)
Dim X As Long, rng as Range, cell as Range
Set rng = ActiveSheet.Range("B2:B" & LR)
For Each cell In rng.Cells
    If cell.Interior.ColorIndex <> -4142 and not isError(cell.value) Then
        myArray(X) = cell.Value
        X = X   1
    End If
Next cell
Redim Preserve myArray(X)    ' Remove unused entries

Set the validiation:

 With Selection.Validation  ' <-- Replace Selection with the Range where you want to apply the validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Join(myArray, ",")
 End With