issues in a For Each ... Next loop

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
I'm having troubles with a For Each ... Next loop.

I'm not sure how much of my code I should post so I'll start on the lean side and if anyone needs more context - just ask.

Code:
For Each cell In Sheets("database").Columns("A")
    If cell.Value = TextBox7.Text Then
        n = n + 1
        If n > UBound(a, 2) Then ReDim Preserve a(1 To 8, 0 To n)
        a(1, n) = cell.Row
    End If
Next

The error message is "Run-time error '13': Type mismatch", generated by the second line I posted.

I have a hunch the error is coming from the cell.value section.

If anyone can shed light on the situation, please let me know.

Thanks.

Straws
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123

ADVERTISEMENT

Ok, so I have a userform which has (among other things) 3 fields to search my database for records of certain date criteria.
the first field determines which of four conditions are relevant so it is a combobox which essentially has 4 options:
  • Equals
    Is before
    Is after
    Is between
These options are listed on a sheet from cell O25 through O28 and the combobox refers to those cells to get it's drop-down list.

Fields 2 and 3 are for data entry of a date in the format dd/mm/yyyy (by default they have "dd/mm/yyyy" in them to show the user the required format).
Field 2 is used regardless of the option chosen in field 1
Field 3 is only used to define the upper limit in the case of option 4 being selected in field 1.

Then, as the code searches through the database for the criteria provided, it enters the row number of each match into a new row in an array.

I have a title row in my database.

The actual names of the three fields I have been talking about are:
Field 1 - combobox1
Field 2 - textbox7
Field 3 - textbox8


My code is as follows:
Code:
Private Sub CommandButton1_Click()
    Dim dr As Range, ff As String, a(), n As Long, i As Integer, txt As String
    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
        n = 0
        If ComboBox1.Text = Sheets("Invoice").Range("O25").Value Then
            For Each cell In Sheets("database").Columns("A")
                If cell.Value = TextBox7.Text Then
                    n = n + 1
                    If n > UBound(a, 2) Then 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 = n + 1
                    If n > UBound(a, 2) Then 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 = n + 1
                    If n > UBound(a, 2) Then 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 = n + 1
                    If n > UBound(a, 2) Then ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = cell.Row
                End If
            Next
        End If
    End If
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Does this do?
Code:
Private Sub CommandButton1_Click()
    Dim dr As Range, ff As String, a(), n As Long, i As Integer, txt As String
    ReDim a(1 To 8, 0 To 0), rng As Range
'check "Date" field for entry, search database, enter results into array column 1
    If TextBox7.Text <> "" And IsDate(TextBox7.Text) Then
        n = 0
        With Sheets("database")
           Set rng = .Range("a1",.Range("a" & Rows.Count).End(xlUp))
        End With
        Select Case ComboBox1.ListIndex
            Case 0
            For Each cell In rng
                If cell.Value = DateValue(TextBox7.Text) Then
                    n = n + 1
                    If n > UBound(a, 2) Then ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = cell.Row
                End If
            Next
           Case 1
            For Each cell In rng
                If cell.Value < DateValue(TextBox7.Text) Then
                    n = n + 1
                    If n > UBound(a, 2) Then ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = cell.Row
                End If
            Next
           Case 2 
            For Each cell In rng
                If cell.Value > DateValue(TextBox7.Text) Then
                    n = n + 1
                    If n > UBound(a, 2) Then ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = cell.Row
                End If
            Next
           Casse 3
            For Each cell In rng
                If cell.Value > DateValue(TextBox7.Text) And cell.Value < DateValue(TextBox8.Text) Then
                    n = n + 1
                    If n > UBound(a, 2) Then ReDim Preserve a(1 To 8, 0 To n)
                    a(1, n) = cell.Row
                End If
            Next
         End Select
        End If
    End If
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
uh yeah that works great.


...


I'm completely baffled as to why.

I've gone through and looked at the things you changed and I can't see why any of them would have made the difference they did.

I tried just changing my line:
Code:
If cell.Value < TextBox7.Text Then
to your line:
Code:
If cell.Value = DateValue(TextBox7.Text) Then
but that made no difference... I had to change the whole thing.

can you please shed any light on what made the difference so that I can avoid the same mistake next time?
 

jindon

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

Do you remember what I said earlier in your other thread that is;

if you need to compare the value in cell with TEXT in the textbox,
we need the data type.

Value in TextBox is always String type data
e.g.
if you need to compare the value in cell that is Numeric then
you need to convert the value in TextBox like Val(TextBox1.Text)
so that you can compare like

If cell.value > Val(TextBox1.Text) Then....

same to Date type
DateValue function converts date in String to Date (refer vb help for detail)

helps?
 

StrawS

Board Regular
Joined
Jul 31, 2006
Messages
123
yeah, that's all good... but, why didn't it work when i did that stuff i described in my last reply:
I tried just changing my line:
Code:
If cell.Value < TextBox7.Text Then
to your line:
Code:
If cell.Value = DateValue(TextBox7.Text) Then
but that made no difference... I had to change the whole thing.
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top