Getting VBA runtime error when putting cell value in to array

Question

I'm trying to find out if a value exists in a range of cells and, if not, run a function. I am using Excel 2007.

I have two problems however -

I'm am getting an error Run-time error 9 - Subscript out of range on the line titles(i) = cell.Value.
I'm unaware of a one liner that can be used in an If statement to check if a value exists in an array.

Here is my code so far. Any pointers on how to solve these problems, or tips on approaches that maybe better, would be grately apprciated. Thanks.

Sub start()

    Dim title_range As Range        ' The range that contains the column titles
    Dim cell As Range               ' The individual cell from the title range
    Dim titles() As String          ' The column titles
    Dim i As Integer                ' Dummy for count

    ' Set the column titles range
    Set title_range = ActiveWorkbook.Sheets("DJG Marketing - Client List by ").Range("A1:DZ1")
    i = 0
    For Each cell In title_range
        titles(i) = cell.Value
        i = i + 1
        ReDim Preserve titles(i)
    Next

    If {value 'Matter open month' does not exist in the array `titles`} Then
        create_month_column
    End If

End Sub

score 3 · Accepted Answer · answered Sep 28 '12 at 10:01

3

Try this code:

i = 0
ReDim titles(i) 'this is missing ;)
For Each cell In title_range
    titles(i) = cell.Value
    i = i + 1
    ReDim Preserve titles(i)
Next

answered Sep 28 '12 at 10:01

Jook

4,564
3
26
53

Many thanks, that is the first part resolved. Any tips for the second part at all? – David Gard Sep 28 '12 at 10:42
@DavidGard: See this http://stackoverflow.com/questions/11109832/how-to-find-if-an-array-contains-a-string – Siddharth Rout Sep 28 '12 at 10:50
Thank you, this has sorted my issues. – David Gard Sep 28 '12 at 11:00

score 2 · Answer 2 · answered Sep 28 '12 at 10:01

2

You are getting that error because you are trying to assign a value before initializing the array

Dim titles() As String
i = 0
For Each cell In title_range
    titles(i) = cell.Value
    '
    '
Next

answered Sep 28 '12 at 10:01

Siddharth Rout

147,039
17
206
250

Thanks, was unaware I had to do this. – David Gard Sep 28 '12 at 10:44

score 0 · Answer 3 · answered Sep 28 '12 at 09:57

0

Doesn't VBA starts with Index 1 in Arrays?

Besides that, you can always check the bounds with LBound and UBound.

answered Sep 28 '12 at 09:57

321X

3,153
2
30
42

1

in vba you have always to specify your array bounds, so it is up to you. But as Siddarth pointed out, David used a dynamic array and did not initialize it, so it had no bounds at all. – Jook Sep 28 '12 at 10:08

Getting VBA runtime error when putting cell value in to array

3 Answers3