add cell address to array

[Luke777]

hi all,

How would I go about adding the address of a cell to an array?

To be specific, I'm testing cells in a range for a value (lets call it X). If that value is true Then add cell.address to array. I'm using an array because I would like to work with all cells where X = true later down the line. I *Think* creating an array is my best option as I cannot assign each its own variable as I do not know how many cells where this value will be true there are.

Thanks all!

 

[rollis13]

You could adapt this method:

Dim myArr()  As String

'to add the first item (0) when still empty
ReDim Preserve myArr(0)
myArr(0) = "$G$15"

'then keep adding with UBound(myArr) + 1
ReDim Preserve arr(UBound(myArr) + 1)
myArr(UBound(myArr)) = "$D$5"

 

[Luke777]

You could adapt this method:

Dim myArr()  As String

'to add the first item (0) when still empty
ReDim Preserve myArr(0)
myArr(0) = "$G$15"

'then keep adding with UBound(myArr) + 1
ReDim Preserve arr(UBound(myArr) + 1)
myArr(UBound(myArr)) = "$D$5"

Thanks

I tried something like

        For Each cell In Range(Cells(searchRow, 3), Cells(searchRow, lastCol))
            If Application.CountIf(cell.Resize(tSlots, 1), "teststring") = tSlots Then 'tSlots = 4 in the test but that shouldnt affect what I'm trying to do with the Then.
                ReDim Preserve myArray(UBound(myArray) + 1) 'I think here I'm missing the initial first item?
                myArray(UBound(myArray)) = cell.Address 
            Else: Cells(searchRow, lastCol + 1) = "false" 'convert to add 'ignore this line
            End If
        Next cell

but I can't see as I'm entering anything into my array correctly

 

[Luke777]

Thanks

I tried something like

        For Each cell In Range(Cells(searchRow, 3), Cells(searchRow, lastCol))
            If Application.CountIf(cell.Resize(tSlots, 1), "teststring") = tSlots Then 'tSlots = 4 in the test but that shouldnt affect what I'm trying to do with the Then.
                ReDim Preserve myArray(UBound(myArray) + 1) 'I think here I'm missing the initial first item?
                myArray(UBound(myArray)) = cell.Address
            Else: Cells(searchRow, lastCol + 1) = "false" 'convert to add 'ignore this line
            End If
        Next cell

but I can't see as I'm entering anything into my array correctly

To

Thanks

I tried something like

        For Each cell In Range(Cells(searchRow, 3), Cells(searchRow, lastCol))
            If Application.CountIf(cell.Resize(tSlots, 1), "teststring") = tSlots Then 'tSlots = 4 in the test but that shouldnt affect what I'm trying to do with the Then.
                ReDim Preserve myArray(UBound(myArray) + 1) 'I think here I'm missing the initial first item?
                myArray(UBound(myArray)) = cell.Address
            Else: Cells(searchRow, lastCol + 1) = "false" 'convert to add 'ignore this line
            End If
        Next cell

but I can't see as I'm entering anything into my array correctly

okay, fixed that to...

With ws1
        For Each cell In Range(Cells(searchRow, 3), Cells(searchRow, lastCol))
            If Application.CountIf(cell.Resize(tSlots, 1), "Busiest Unhosted") = tSlots Then
                ReDim Preserve myArray(0)
                myArray(0) = cell.Address
            Else: Cells(searchRow, lastCol + 1) = "false" 'convert to add
            End If
        Next cell

Which always replaces the same part of the array with the next result address (improvement on not working at all). I'm a little unsure how to tell it if myArray(0) is full, then +1

 

[rollis13]

You missed the first part of my suggestion, Ubound can't be used on an empty array so this could be a solution:

ReDim Preserve myArray(0)
For Each cell In Range(Cells(searchRow, 3), Cells(searchRow, lastCol))
    If Application.CountIf(cell.Resize(tSlots, 1), "teststring") = tSlots Then
        If myArray(0) = 0 Then                'to add the first item (0) when still empty
            myArray(0) = cell.Address
        Else
            ReDim Preserve myArray(UBound(myArray) + 1) 'then keep adding with UBound(myArr) + 1
            myArray(UBound(myArray)) = cell.Address
        End If
    Else
        Cells(searchRow, lastCol + 1) = "false" 'convert to add 'ignore this line
    End If
Next cell

 

[Luke777]

You missed the first part of my suggestion, Ubound can't be used on an empty array so this could be a solution:

ReDim Preserve myArray(0)
For Each cell In Range(Cells(searchRow, 3), Cells(searchRow, lastCol))
    If Application.CountIf(cell.Resize(tSlots, 1), "teststring") = tSlots Then
        If myArray(0) = 0 Then                'to add the first item (0) when still empty
            myArray(0) = cell.Address
        Else
            ReDim Preserve myArray(UBound(myArray) + 1) 'then keep adding with UBound(myArr) + 1
            myArray(UBound(myArray)) = cell.Address
        End If
    Else
        Cells(searchRow, lastCol + 1) = "false" 'convert to add 'ignore this line
    End If
Next cell

Looks really promising but I'm getting a type mismatch on the line "If myArray(0) = 0 Then" for some reason - I'm not sure what error this is... its certainly finding the string and the array name is correct

 

[rollis13]

The array must be declared Variant, not String as I suggested in post #2.

Dim myArray() As Variant

 

[Luke777]

The array must be declared Variant.

Dim myArray() As Variant

you beautiful individual you! Works like a charm

Thank you so much for your help!

 

[rollis13]

Glad having been of some help. Sorry for the String but it was a generic suggestion.