Ubound returns "subscript out of range"

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Hi,

I'm having trouble with one line of my project, it's the second to last line, and it generates the following error
Run-time error '9':
Subscript out of range

I think I'm using the right syntax for the Ubound command, but I'm not entirely sure. If anyone could take the time to check it out that'd be great.

Thank you



Here is my code:
Code:
Private Sub CommandButton1_Click()
    Dim dr As Range, ff As String, a(), n As Long, i As Integer
    If TextBox7.Text <> "" And TextBox7.Text <> "dd/mm/yyyy" Then
        If ComboBox1.Text = Sheets("Invoice").Range("O25").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value = TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 1 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O26").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value < TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O27").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value > TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O28").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value > TextBox7.Text And Cell.Value < TextBox8.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        End If
    End If
        
    
    If Me.TextBox6.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("C2:" & .Cells(65536, 3).End(xlUp).Address).Find(Me.TextBox6.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(2, n) = dr.Row
                    dr = .Range("C2:" & .Cells(65536, 3).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
    If Me.TextBox5.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("D2:" & .Cells(65536, 4).End(xlUp).Address).Find(Me.TextBox5.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(3, n) = dr.Row
                    dr = .Range("D2:" & .Cells(65536, 4).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
    If Me.TextBox4.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("F2:" & .Cells(65536, 6).End(xlUp).Address).Find(Me.TextBox4.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(4, n) = dr.Row
                    dr = .Range("F2:" & .Cells(65536, 6).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
    If Me.ComboBox3.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("H2:" & .Cells(65536, 8).End(xlUp).Address).Find(Me.ComboBox3.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(5, n) = dr.Row
                    dr = .Range("H2:" & .Cells(65536, 8).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
    If Me.TextBox3.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("I2:" & .Cells(65536, 45).End(xlUp).Address).Find(Me.TextBox3.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(6, n) = dr.Row
                    dr = .Range("I2:" & .Cells(65536, 45).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
    If Me.TextBox2.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("DP2:" & .Cells(65536, 120).End(xlUp).Address).Find(Me.TextBox2.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(7, n) = dr.Row
                    dr = .Range("DP2:" & .Cells(65536, 120).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
    If TextBox1.Text <> "" Then
        If ComboBox2.Text = Sheets("Invoice").Range("O20").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value = TextBox1.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox2.Text = Sheets("Invoice").Range("O21").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value < TextBox1.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox2.Text = Sheets("Invoice").Range("O22").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value > TextBox1.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox2.Text = Sheets("Invoice").Range("O23").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value > TextBox1.Text And Cell.Value < TextBox9.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        End If
    End If
    
    
    
    
    MsgBox UBound(a, 2)
 End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi StrawS

I may be wrong but you should get that error much before. You are systematically using UBound before ReDim.

UBound gives you the highest index in one dimension.
Ex:

Dim a()

ReDim Preserve a(1 To 8, 1 To 9)
UBound(a, 1) equal to 8
Ubound(a, 2) equal to 9

If you use UBound before the ReDim, the array does not exist yet and you get the error.

Hope I'm not missing something
PGC
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
You can't re-dimention the array which is already dementioned as

a(1 To 8, 1 To n)

to

a(1 To 8, 0 To n)

Furthermore,

Since you didn't dimention the array before the If statement

UBound(a,2) itself create runtime error...
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Problem fixed! now I get a message box with the upperbound limit.
However, it always outputs "0" regardless of what the array should be.
Is there any way I can output the whole array so I can see what's going on and troubleshoot from there?

I may be wrong but you should get that error much before. You are systematically using UBound before ReDim.
PGC, I can see what you mean, but it didn't seem to choke on this. It just went straight to that second to last line.


You can't re-dimention the array which is already dementioned as
a(1 To 8, 1 To n)
to
a(1 To 8, 0 To n)
Thanks Jindon, I've missed a few things in the code so far. I hadn't thoroughly tested the code when I posted - I only tested the section that deals with textbox6 and when I found the fault and was baffled so I posted.


Since you didn't dimention the array before the If statement
UBound(a,2) itself create runtime error...
Great, didn't know this was needed, but it's what fixed the problem. Now I'm just stuck on why it's only returning "0" regardless of what results it should have picked up.


Any further ideas would be great. If I need to post my revised code or give any more info then just let me know.

Thanks
Straws
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Straws

Do you want to post your current code?
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Sure:

Code:
Private Sub CommandButton1_Click()
    Dim dr As Range, ff As String, a(), n As Long, i As Integer
    ReDim a(1 To 8, 0 To 0)

'check "Date" field for entry, search database, enter results into array column 1
    If TextBox7.Text <> "" And TextBox7.Text <> "dd/mm/yyyy" Then
        If ComboBox1.Text = Sheets("Invoice").Range("O25").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value = TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O26").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value < TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O27").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value > TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O28").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value > TextBox7.Text And Cell.Value < TextBox8.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        End If
    End If
        
    
'check "Customer Name" field for entry, search database, enter results into array column 2
    If Me.TextBox6.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("C2:" & .Cells(65536, 3).End(xlUp).Address).Find(Me.TextBox6.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(2, n) = dr.Row
                    dr = .Range("C2:" & .Cells(65536, 3).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
'check "Street Address" field for entry, search database, enter results into array column 3
    If Me.TextBox5.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("D2:" & .Cells(65536, 4).End(xlUp).Address).Find(Me.TextBox5.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(3, n) = dr.Row
                    dr = .Range("D2:" & .Cells(65536, 4).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
'check "City" field for entry, search database, enter results into array column 4
    If Me.TextBox4.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("F2:" & .Cells(65536, 6).End(xlUp).Address).Find(Me.TextBox4.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(4, n) = dr.Row
                    dr = .Range("F2:" & .Cells(65536, 6).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
'check "Transaction Code" field for entry, search database, enter results into array column 5
    If Me.ComboBox3.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("H2:" & .Cells(65536, 8).End(xlUp).Address).Find(Me.ComboBox3.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(5, n) = dr.Row
                    dr = .Range("H2:" & .Cells(65536, 8).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
'check "Item Description" field for entry, search database, enter results into array column 6
    If Me.TextBox3.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("I2:" & .Cells(65536, 45).End(xlUp).Address).Find(Me.TextBox3.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(6, n) = dr.Row
                    dr = .Range("I2:" & .Cells(65536, 45).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
'check "Notes" field for entry, search database, enter results into array column 7
    If Me.TextBox2.Text <> "" Then
        With Sheets("Database")
            Set dr = .Range("DP2:" & .Cells(65536, 120).End(xlUp).Address).Find(Me.TextBox2.Text, , , xlWhole)
            If Not dr Is Nothing Then
                ff = dr.Address
                Do
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(7, n) = dr.Row
                    dr = .Range("DP2:" & .Cells(65536, 120).End(xlUp).Address).FindNext(dr)
                Loop Until ff = dr.Address
            End If
        End With
    End If
    
    
'check "Total Price" field for entry, search database, enter results into array column 8
    If TextBox1.Text <> "" Then
        If ComboBox2.Text = Sheets("Invoice").Range("O20").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value = TextBox1.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox2.Text = Sheets("Invoice").Range("O21").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value < TextBox1.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox2.Text = Sheets("Invoice").Range("O22").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value > TextBox1.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox2.Text = Sheets("Invoice").Range("O23").Value Then
            For Each Cell In Sheets("database").Range("DR2:" & Sheets(database).Cells(65536, 122).End(xlUp).Address)
                If Cell.Value > TextBox1.Text And Cell.Value < TextBox9.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(8, n) = Cell.Row
                End If
            Next
        End If
    End If
    
    
    
    MsgBox UBound(a, 2)
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

OK,
Let's go through part by part

Code:
Private Sub CommandButton1_Click()
    Dim dr As Range, ff As String, a(), n As Long, i As Integer
    ReDim a(1 To 8, 0 To 0)

'check "Date" field for entry, search database, enter results into array column 1
    If TextBox7.Text <> "" And TextBox7.Text <> "dd/mm/yyyy" Then
        If ComboBox1.Text = Sheets("Invoice").Range("O25").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value = TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O26").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value < TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O27").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value > TextBox7.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        ElseIf ComboBox1.Text = Sheets("Invoice").Range("O28").Value Then
            For Each Cell In Sheets("database").Range("A2:" & Sheets(database).Cells(65536, 1).End(xlUp).Address)
                If Cell.Value > TextBox7.Text And Cell.Value < TextBox8.Text Then
                    n = UBound(a, 2) + 1
                    ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = Cell.Row
                End If
            Next
        End If
    End If
This part only fill the 1st row of 1st column of the array and nothing else.
What do you wnat to do with this part?
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
To answer your question I would have to explain the whole code instead of part by part.

In any case, do we need to go through it part by part in order to solve the problem?

I'm guessing that either the code isn't inputting data to the array properly, or the Ubound function at the end isn't working properly.

regarding the section you quoted - I do intend for it to only fill column 1. Each section fills a different column (1 through to 8).

Can you help me with the problem of the last Ubound function returning "0" regardless of how many matches the searched data contains?

Straws
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
StrawS

To solve the problem, we need to understand what you are really trying to do.


It is too hard for us to talk about problem based on the code that is not functioning.

eg. what is TextBox7 <> "dd/mm/yy" ?
do you expect that like Not IsDate(TextBox7.Text) ?

when you comparing textbox.text with cell.value, we need to know the data type.

etc.

You can learn from the code that is functioning, but never from the code that is not functioning.

P.S
According to your explanation, you don't need to have 8 rows in the array.
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
Ok, we'll start again and I'll make it easier.

Lets take a database as follows:
A1 = First Name
A2 = John
A3 = Joe
A4 = John
A5 = Tom
A6 = Joe
B1 = Last Name
B2 = Smith
B3 = Blogs
B4 = Doe
B5 = Jones
B6 = Thompson

Then we have a userform that is being designed to allow us to search the records in this database for certain criteria.
There are two textboxes and a commandbutton in the userform.
The user enters a name into either (or both) of these textboxes and then clicks the userbutton to search.

The following code lies behind the userform:
Code:
Private Sub CommandButton1_Click() 
    Dim dr As Range, ff As String, a(), n As Long, i As Integer 
    ReDim a(1 To 2, 0 To 0) 

'check "First Name" field for entry, search database, enter results into array column 1 
    If Me.TextBox1.Text <> "" Then 
        With Sheets("Database") 
            Set dr = .Range("A2:" & .Cells(65536, 1).End(xlUp).Address).Find(Me.TextBox1.Text, , , xlWhole) 
            If Not dr Is Nothing Then 
                ff = dr.Address 
                Do 
                    n = UBound(a, 2) + 1 
                    ReDim Preserve a(1 To 2, 0 To n) 
                    a(1, n) = dr.Row 
                    dr = .Range("A2:" & .Cells(65536, 1).End(xlUp).Address).FindNext(dr) 
                Loop Until ff = dr.Address 
            End If 
        End With 
    End If 
    
    
'check "Last Name" field for entry, search database, enter results into array column 2 
    If Me.TextBox2.Text <> "" Then 
        With Sheets("Database") 
            Set dr = .Range("B2:" & .Cells(65536, 2).End(xlUp).Address).Find(Me.TextBox2.Text, , , xlWhole) 
            If Not dr Is Nothing Then 
                ff = dr.Address 
                Do 
                    n = UBound(a, 2) + 1 
                    ReDim Preserve a(1 To 2, 0 To n) 
                    a(2, n) = dr.Row 
                    dr = .Range("B2:" & .Cells(65536, 2).End(xlUp).Address).FindNext(dr) 
                Loop Until ff = dr.Address 
            End If 
        End With 
    End If 
    
    
    MsgBox UBound(a, 2) 
End Sub

I haven't finished this project, but am just stuck at this particular stage: On the second to last line of this code I have put a message box with a Ubound command to show me how many rows exist in the array that is created.

The problem I am posting about is that regardless of how many records the search criteria match, the results always come back as "0".

Does anyone see a reason?

Thanks for your time
 

Watch MrExcel Video

Forum statistics

Threads
1,113,850
Messages
5,544,650
Members
410,627
Latest member
georgealice
Top