Hi,
I'm having trouble with one line of my project, it's the second to last line, and it generates the following error
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:
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