| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find function Help
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 '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 The Code copys everything ok with a exact match in the search. also for some reason the search is case sensitve. i am told i need to use the find function here not sure how to implament it. i looked the function up but commin from Access VBA its cryptic to me. i Know where it needs to go but im not sure how it works and what i need to remove. goes he \ If Sheets("ImmageLocations").Cells(Row, Col) Like SearchFor Then \ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
find function Help
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find function | Excel Worksheet Functions | |||
| Find function | Excel Worksheet Functions | |||
| If find function to not find anything | Excel Programming | |||
| Find function | Excel Worksheet Functions | |||
| backwards find function to find character in a string of text | Excel Programming | |||
Linear Mode
