ExcelBanter - View Single Post

JBurlison

Hello im new to excel VBA i come from a strong Access VBA backround here is
what im tring to do:

Im Trying to search for something on another sheet and copy it over to the
search sheet as the search results.

For the search id like it to return anything that has for example the word
"test" in it

so if i put "Test" in the search box i want it to recongnize things like
"Testing1", "initial Tests", "Test Name 1". ect..

if it has "test" anywhere in the cell i want it to return.

this only dose exact maches. any idea how i can get it to do this?

Now i was told about a find function im not sure how to implament it. i
looked up the function and im a little confused on it.

heres another help thread i posted it has the excel file.
http://www.access-programmers.co.uk/...099#post805099

im using excel 2003

Code:

Sub clickSearch()
    Dim Row As Integer
    Dim Col As Integer
    Dim RowCopy As Integer
    Dim ColCopy As Integer
    Dim Copy As Integer
    Dim CopyCellA As String
    Dim CopyCellB As String
    Dim CopyCellC As String
    Dim SearchFor
    Dim ClrRg
'Clears the search results fields
    Set ClrRg = Union(Range("A2:A1000"), Range("B2:B1000"), Range("C2:C1000"))
    ClrRg.Clear
'States what you are searching for (via the search box)
    SearchFor = Cells(4, 6)
    Copy = 1
    RowCopy = 1
    ColCopy = 1
    Row = 2
    Col = 1
 
 
RunColA:
'Clears any copied infromation
    CopyCellA = ""
    CopyCellB = ""
    CopyCellC = ""
'Checks the cell for the search information, then checks the collum it is 
currently in to copy and pase the correct information
    If Sheets("ImmageLocations").Cells(Row, Col) Like SearchFor Then
            If Col = 1 Then
                CopyCellA = Sheets("ImmageLocations").Cells(Row, Col)
                CopyCellB = Sheets("ImmageLocations").Cells(Row, Col + 1)
                CopyCellC = Sheets("ImmageLocations").Cells(Row, Col + 2)
            Else
                If Col = 2 Then
                    CopyCellA = Sheets("ImmageLocations").Cells(Row, Col - 1)
                    CopyCellB = Sheets("ImmageLocations").Cells(Row, Col)
                    CopyCellC = Sheets("ImmageLocations").Cells(Row, Col + 1)
                Else
                    If Col = 3 Then
                        CopyCellA = Sheets("ImmageLocations").Cells(Row, Col 
- 2)
                        CopyCellB = Sheets("ImmageLocations").Cells(Row, Col 
- 1)
                        CopyCellC = Sheets("ImmageLocations").Cells(Row, Col)
                    End If
                End If
            End If
        If Col = 1 Then
            Sheets("Search").Cells(RowCopy + Copy, Col) = CopyCellA
            Sheets("Search").Cells(RowCopy + Copy, Col + 1) = CopyCellB
            Sheets("Search").Cells(RowCopy + Copy, Col + 2) = CopyCellC
        Else
            If Col = 2 Then
                Sheets("Search").Cells(RowCopy + Copy, Col - 1) = CopyCellA
                Sheets("Search").Cells(RowCopy + Copy, Col) = CopyCellB
                Sheets("Search").Cells(RowCopy + Copy, Col + 1) = CopyCellC
            Else
                If Col = 3 Then
                    Sheets("Search").Cells(RowCopy + Copy, Col - 2) = 
CopyCellA
                    Sheets("Search").Cells(RowCopy + Copy, Col - 1) = 
CopyCellB
                    Sheets("Search").Cells(RowCopy + Copy, Col) = CopyCellC
                End If
            End If
        End If
'Moves to the next Row
        Copy = Copy + 1
        Row = 1 + Row
        GoTo RunColA
    Else
        Row = 1 + Row
'Checks to see if it has searched all fields in that collum, if so resets 
rows and moved to the next collum
        If Sheets("ImmageLocations").Cells(Row, Col) = "" Or 
IsNull(Sheets("ImmageLocations").Cells(Row, Col)) Then
            Col = 1 + Col
            Row = 2
            If Col = 4 Then
                GoTo Endsub
            Else
                GoTo RunColA
            End If
        Else
             GoTo RunColA
        End If
    End If
Endsub:
End Sub

if you can sift thrugh the mess here is where im having my problem:

If Sheets("ImmageLocations").Cells(Row, Col) Like SearchFor Then

the "Like" function is not the correct use here im told i need to use the
find function.

this code works but only exact matches. (even case)

i need case not to be a issue.