Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with Find Function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Help with Find Function
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.






Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default Help with Find Function
Look up Find and FindNext in VBA help, it will save the loops and you can do
part matches.

--
__________________________________
HTH

Bob

"JBurlison" wrote in message
...
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.








Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Help with Find Function
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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default Help with Find Function
It's not you, it is the forum. It has a problem.

--
__________________________________
HTH

Bob

"JBurlison" wrote in message
...
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.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5
Default Help with Find Function
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" wrote:

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

--
__________________________________
HTH

Bob

"JBurlison" wrote in message
...
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.









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find function?? SeniorJosie Excel Worksheet Functions 3 June 1st 09 09:29 PM
If find function to not find anything Carlos Excel Programming 4 February 14th 08 09:25 AM
When using the find function....... CarolineHedges[_20_] Excel Programming 3 August 1st 06 01:48 PM
Find function Jahunga Excel Worksheet Functions 2 November 22nd 04 03:38 PM
backwards find function to find character in a string of text Ashleigh K. Excel Programming 1 January 14th 04 04:36 PM


All times are GMT +1. The time now is 03:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement