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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top