Search Macro Help (1 Viewer)

J

JBurlison

Registered User.
Local time
Today, 02:56
Joined
Mar 14, 2008
Messages
172
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?


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
[COLOR=yellowgreen]'Clears the search results fields[/COLOR]
    Set ClrRg = Union(Range("A2:A1000"), Range("B2:B1000"), Range("C2:C1000"))
    ClrRg.Clear
[COLOR=yellowgreen]'States what you are searching for (via the search box)[/COLOR]
    SearchFor = Cells(4, 6)
    Copy = 1
    RowCopy = 1
    ColCopy = 1
    Row = 2
    Col = 1
 
 
RunColA:
[COLOR=yellowgreen]'Clears any copied infromation[/COLOR]
    CopyCellA = ""
    CopyCellB = ""
    CopyCellC = ""
[COLOR=yellowgreen]'Checks the cell for the search information, then checks the collum it is currently in to copy and pase the correct information[/COLOR]
  [COLOR=red]  If Sheets("ImmageLocations").Cells(Row, Col) Like SearchFor Then[/COLOR]
            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
[COLOR=yellowgreen]'Moves to the next Row[/COLOR]
        Copy = Copy + 1
        Row = 1 + Row
        GoTo RunColA
    Else
        Row = 1 + Row
[COLOR=yellowgreen]'Checks to see if it has searched all fields in that collum, if so resets rows and moved to the next collum[/COLOR]
        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

The Code copys everything ok with a exact match in the search.

EDIT: Inclueded the file, also for some reason the search is case sensitve.
 

Attachments

  • ImmageLocations.xls
    72.5 KB · Views: 171
Last edited:
chergh

chergh

blah
Local time
Today, 07:56
Joined
Jun 15, 2004
Messages
1,414
Why are you not using the find method?
 
J

JBurlison

Registered User.
Local time
Today, 02:56
Joined
Mar 14, 2008
Messages
172
chergh said:
Why are you not using the find method?
Click to expand...


I Need to copy the ajacent cells for each instance of the word without copying the same row multiple times (if the word apears in that row more than once)

not too clear on the find function if its possible with this.
 
chergh

chergh

blah
Local time
Today, 07:56
Joined
Jun 15, 2004
Messages
1,414
That shouldn't stop you from using the find method. I don''t have much time at the moment but if you create a new collection and then whenever you find a match add the row number to it and also add the row number as the key for collection item then you can use error handling to avoid copying the same row multiple times.
 
J

JBurlison

Registered User.
Local time
Today, 02:56
Joined
Mar 14, 2008
Messages
172
i have never used the Find Function im comming from Access VBA so this stuff is new to me

I looked up the find function and im not quite sure how to implament it into the code.
 
J

JBurlison

Registered User.
Local time
Today, 02:56
Joined
Mar 14, 2008
Messages
172
So i looked up the find function:

here is the example it gives
Code: 
With Worksheets(1).Range("a1:a500")
        Set c = .Find(2, lookin:=xlValues)
        If Not c Is Nothing Then
               firstAddress = c.Address
        Do
               c.Value = 5
               Set c = .FindNext(c)
           Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
End With

Is "firstAddress = c.Address" relavent to the procedure?
I dont understand how this function works.
First your decalring the worksheet and the range of cells you wish to search in.

Then you set "c" to find the number 2
(if not c is nothing) dose this mean if the number 2 is not found in the cell?

firstAddress = c.Address (i dont understand this at all is the a variable "firstaddress" and "Address" or are they functions of some kind? i dont understand the purpose of this line.)

"Do" (tating if the number 2 is found)

c.value=5 (replaces with the number 5)

Set c = .FindNext(c) (finds next)

Loop While Not c Is Nothing And c.Address <> firstAddress (lost here too)
and if "c.Address" and "firstAddress" are variables what would they cells? what im searching for?

im just trying to figure out how to add this into my code.
 
Last edited:
Brianwarnock

Brianwarnock

Retired
Local time
Today, 07:56
Joined
Jun 2, 2003
Messages
12,701
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
c would be the first cell where 2 is found
If Not c Is Nothing Then
If 2 is not found in the range nothing is returned and thus the sub will be exited
firstAddress = c.Address address of 1st cell where 2 is found .Address gives the address of a cell
Do
c.Value = 5 change value
Set c = .FindNext(c) find next 2
Loop While Not c Is Nothing And c.Address <> firstAddress
I also don't understand why the Address check is made here as if c is not nothing it will have changed
End If
End With
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 07:56
Joined
Jun 2, 2003
Messages
12,701
I decided to test that code today and got an error after the last 2 had been found. I took the
Code: 
And c.Address <> firstAddress
out and it worked fine.

Brian
 
You must log in or register to reply here.

Similar threads

S
VBA How to copy ONLY non blank cells
Replies
1
Views
349
Gasman
Gasman
D
Copy only selected fields from 1 sheet to another
Replies
19
Views
552
dcavaiani
D
Z
Can you change this code or make it easy or suggest another code
Replies
2
Views
431
zezo2021
Z
A
Copy Selected (Protected - Column Headers) Rows/Cells Into Body Of The Email As Image
Replies
3
Views
468
Gasman
Gasman
P
why the sorting Doesn't Happen?
Replies
4
Views
370
MarkK
MarkK

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom