VBA Search with multi column/results

[Eryrok]

I have searched several sites with no avail. If anyone can see what I'm doing wrong in this VBA code I would greatly appreciate it. I am getting an object required error at:

Set c = .Find(strFind, LookIn:=xlValues)

Private Sub SearchData()
    Dim strFind As String
    Dim FirstAddress As String
    Dim rSearch As Range
    Dim f As Integer
    Dim a As Integer
    Dim n As Integer
    Dim c As String
    Dim b As String
    f = 0
    a = 0
    n = 1
 
    If Not Me.UNID = Empty Then
        a = 1
        Set rSearch = Sheets("Data").Range("d1", Range("d65536").End(xlUp))
        strFind = Me.UNID.Value
        GoTo SearchData2
    End If
    If Not Me.WO = Empty Then
        a = 2
        Set rSearch = Sheets("Data").Range("b2", Range("b65536").End(xlUp))
        strFind = Me.WO.Value
        GoTo SearchData2
    End If
    If Not Me.System = Empty Then
        a = 3
        Set rSearch = Sheets("Data").Range("c1", Range("c65536").End(xlUp))
        strFind = Me.System.Value
        GoTo SearchData2
    End If
    If Not Me.EDCR = Empty Then
        Set rSearch = Sheets("Data").Range("a1", Range("a65536").End(xlUp))
        strFind = Me.EDCR.Value
        GoTo SearchData2
    End If
    If Not Me.Status = Empty Then
        a = 5
        Set rSearch = Sheets("Data").Range("g1", Range("g65536").End(xlUp))
        strFind = Me.Status.Value
        GoTo SearchData2
    End If
 
SearchData2:
    With rSearch
        Set [COLOR=blue][B]c =[/B][/COLOR] .Find(strFind, LookIn:=xlValues)
        Set b = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then
            c.Select
            If a = 1 Then c = c.Offset(0, -3).Select
            If a = 2 Then c = c.Offset(0, -1).Select
            If a = 3 Then c = c.Offset(0, -2).Select
            If a = 5 Then c = c.Offset(0, -6).Select
            Do
                With Me.ListBox1
                    .AddItem c.Value
                        .List(.ListCount - n, 1) = c.Offset(0, 1).Value
                        .List(.ListCount - n, 2) = c.Offset(0, 2).Value
                        .List(.ListCount - n, 3) = c.Offset(0, 3).Value
                        .List(.ListCount - n, 4) = c.Offset(0, 4).Value
                        .List(.ListCount - n, 5) = c.Offset(0, 5).Value
                        .List(.ListCount - n, 6) = c.Offset(0, 6).Value
                        .List(.ListCount - n, 7) = c.Offset(0, 7).Value
                        .List(.ListCount - n, 8) = c.Offset(0, 8).Value
                End With
 
                FirstAddress = b.Address
                Do
                    f = f + 1
                    n = n + 1
                    Set c = .FindNext(c)
                    Set b = .FindNext(b)
                    Loop While Not c Is Nothing And b.Address <> FirstAddress
        Else: MsgBox strFind & " not listed"
        End If
    End With
End Sub

 

[Rick Rothstein]

I have searched several sites with no avail. If anyone can see what I'm doing wrong in this VBA code I would greatly appreciate it. I am getting an object required error at:

Set c = .Find(strFind, LookIn:=xlValues) [/CODE]

The problem is you have Dim'med the 'c' variable like this...

Dim c As String

Set is only used with object variables (String variables are not one of them) and the output from the Find function is a Range, not a String. You will have the same problem with the 'b' variable.

 

[Eryrok]

Thanks, after removing the dim c and dim b lines, i had a few small errors easily corrected, but I still can't seem to get it to run through the entire script. It either hangs up on a do with no loop error, or a object required error with the

.additem c.value

. Thanks for the help, and can anyone suggest a good vba code guide or explaination book. I know enough vba to usually do what i need, but I don't understand the why behind the code. such as the dim mistake I previously made.

Thanks,
Eryrok