Easy, quick array questions.

[dwg83]

How do I dim an array with an undetermined length?

How do I add a value from a cell to the end of an array?

How do I determine if a value in a cell is already in the array?

Thanks!

 

[VoG]

For 1) and 2) perhaps this although it isn't the most efficient way of populating an array:

Dim x() As Variant, LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
ReDim Preserve x(1 To LR)
For i = 1 To LR
  x(i) = Range("A" & i).Value
Next i

For 3) do you mean check whether an array contains duplicate values or something else?

 

[GTO]

Deleted...answered (Hi Peter, have a great weekend! )

 

[VoG]

Deleted...answered (Hi Peter, have a great weekend! )

Hi Mark - have a great one yourself

 

[EducatedFool]

How do I determine if a value in a cell is already in the array?

    txt = "myText"
    If "%" & Join(arr, "%") & "%" Like "*%" & txt & "%*" Then MsgBox "text found"
End Sub

 

[dwg83]

Thanks for the responses and questions.

When I use ReDim do I have to go 1 to a number or is there a way to determine the length of the current array and just add 1? i.e. ReDim x(1 to NumElements(x))

And to clarify my last question, I would like to take a value from a cell and see if that value already exists in my array. If it doesn't, I will add it, if it does, I will move on.

I will try educated fool's answer, but it is a bit confusing for me.

thanks

 

[VoG]

a) Try

ReDim Preserve x(1 To UBound(x) + 1)

b) One way

If IsNumeric(Application.Match(Range("A2").Value, x, 0)) Then MsgBox "Match!"

where x is a 1-dimensional array.

 

[dwg83]

VoG,

I currently get a Run-Time error '9' Subscript out of range error and it highlights the ReDim line.

Here is my code. I haven't even gotten to your suggestion for b, I figured I would get this sorted out first.

Thanks!

Private Sub Worksheet_Activate()

Dim LastRow, Marker, i, j As Integer
Dim DI As Worksheet
Dim Arr()

Set DI = Worksheets("Data_Input")


'Finds last row used
LastRow = DI.Range("A65536").End(xlUp).Row
Marker = 2

For i = 4 To LastRow
    If DI.Cells(i, 1) <> "" Then
        If Not "%" & Join(Arr, "%") & "%" Like "*%" & DI.Cells(i, 1).Value & "%*" Then
            ReDim Preserve Arr(1 To UBound(Arr) + 1)
            Arr(UBound(Arr)) = DI.Cells(i, 1).Value
            Cells(Marker + 1, 1) = "ECM"
            Cells(Marker + 1, 2) = DI.Cells(i, 1)
            Cells(Marker + 2, 1) = DI.Cells(i, 2)
            Cells(Marker + 2, 2) = DI.Cells(i, 3)
            Cells(Marker + 2, 3) = DI.Cells(i, 26)
            Cells(Marker + 2, 4) = DI.Cells(i, 27)
            Cells(Marker + 2, 5) = DI.Cells(i, 28)
            Marker = Marker + 2
            For j = i + 1 To LastRow
                If DI.Cells(j, 1).Value = DI.Cells(i, 1).Value Then
                    Cells(Marker + 1, 1) = DI.Cells(j, 2)
                    Cells(Marker + 1, 2) = DI.Cells(j, 3)
                    Cells(Marker + 1, 3) = DI.Cells(j, 26)
                    Cells(Marker + 1, 4) = DI.Cells(j, 27)
                    Cells(Marker + 1, 5) = DI.Cells(j, 28)
                    Marker = Marker + 1
                End If
            Next j
            Marker = Marker + 2
        End If
    End If
Next i
                    

End Sub

 

[VoG]

Try this at the top of your module, outside of any subs:

Option Base 1

 

[dwg83]

I put it above the first line and I get the same result. What was that line supposed to do?

Thanks