Help with Find Function

J

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/forums/showthread.php?p=805099#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.
 
B

Bob Phillips

Look up Find and FindNext in VBA help, it will save the loops and you can do
part matches.

--
__________________________________
HTH

Bob

JBurlison said:
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/forums/showthread.php?p=805099#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.
Click to expand...
 
J

JBurlison

oops kep giving me an errror while i was trying to post it i thought i lost
it all sorry for the multiple posts. can just use this one.
 
J

JBurlison

here is the example it gives

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?
whay im searching for?

im just trying to figure out how to add this into my code.





Bob Phillips said:
Look up Find and FindNext in VBA help, it will save the loops and you can do
part matches.

--
__________________________________
HTH

Bob

JBurlison said:
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/forums/showthread.php?p=805099#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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find function Help 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
A For Each in Range... with a .Find(what:="TJ", ... in it problem 15
using the cells property for use with Application.WorksheetFunction.Sum 2
VLookup using VB 5
Sort and align 2 ranges 1
comment copy 3
Compare data set 1 with data set 2. Add to data set 2 if not found 0

Top