Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Find instead of VLOOKUP in search
Using XL97, I’m seeking to search through a list to find those records
that match a criteria, and then for each matching record, place the result in column 8 of that record. I’ve been seeking to use the find function as it supposedly runs much faster than the VLOOKUP function. The code shown below performs the find function for one occurrence. When I learn how to make it work, I’m confident I can include it in a loop to repeat the operation for multiple occurrences. : HOW DO I set the value of the cell in the eighth column of the matching record to the vOurResult???? If tried things like: Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult But the above line places the result on the line where the cursor happens to be instead of the line containing the matching record. (Code is plagiarized from: http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm) Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "Billy Brown") 0 Then With Worksheets("Sheet1").Range("Data") vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With MsgBox vOurResult End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Find instead of VLOOKUP in search
Try:
Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "BillyBrown") 0 Then With Worksheets("Sheet1").Range("Data") Set vOurResult = .Find(What:="BillyBrown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) End With MsgBox vOurResult.Row Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult End If End Sub "windsurferLA" wrote: Using XL97, Im seeking to search through a list to find those records that match a criteria, and then for each matching record, place the result in column 8 of that record. Ive been seeking to use the find function as it supposedly runs much faster than the VLOOKUP function. The code shown below performs the find function for one occurrence. When I learn how to make it work, Im confident I can include it in a loop to repeat the operation for multiple occurrences. : HOW DO I set the value of the cell in the eighth column of the matching record to the vOurResult???? If tried things like: Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult But the above line places the result on the line where the cursor happens to be instead of the line containing the matching record. (Code is plagiarized from: http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm) Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "Billy Brown") 0 Then With Worksheets("Sheet1").Range("Data") vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With MsgBox vOurResult End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Find instead of VLOOKUP in search
P.S I'm using XL2003 so not so sure how FIND works with XL97.
"windsurferLA" wrote: Using XL97, Im seeking to search through a list to find those records that match a criteria, and then for each matching record, place the result in column 8 of that record. Ive been seeking to use the find function as it supposedly runs much faster than the VLOOKUP function. The code shown below performs the find function for one occurrence. When I learn how to make it work, Im confident I can include it in a loop to repeat the operation for multiple occurrences. : HOW DO I set the value of the cell in the eighth column of the matching record to the vOurResult???? If tried things like: Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult But the above line places the result on the line where the cursor happens to be instead of the line containing the matching record. (Code is plagiarized from: http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm) Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "Billy Brown") 0 Then With Worksheets("Sheet1").Range("Data") vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With MsgBox vOurResult End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Find instead of VLOOKUP in search
This should be close... It looks in column A for "This". It then places your
variable value 8th column over... If that is not quite what you wanted then let me know... I could change it to look at the entire sheet and then place your value in the 8th column but I have always found that type of code a little dangerous as it finds things you never intended it to find... Sub FindAll() Dim wks As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFirst As Range Set wks = Sheets("Sheet1") Set rngToSearch = wks.Columns("A") Set rngFound = rngToSearch.Find("This", , , xlPart) If rngFound Is Nothing Then MsgBox "Sorry... Nothing was found." Else Set rngFirst = rngFound Do wks.Cells( rngFound.row, 8).Value = vOurResult Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = rngFirst.Address End If End Sub -- HTH... Jim Thomlinson "windsurferLA" wrote: Using XL97, Im seeking to search through a list to find those records that match a criteria, and then for each matching record, place the result in column 8 of that record. Ive been seeking to use the find function as it supposedly runs much faster than the VLOOKUP function. The code shown below performs the find function for one occurrence. When I learn how to make it work, Im confident I can include it in a loop to repeat the operation for multiple occurrences. : HOW DO I set the value of the cell in the eighth column of the matching record to the vOurResult???? If tried things like: Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult But the above line places the result on the line where the cursor happens to be instead of the line containing the matching record. (Code is plagiarized from: http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm) Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "Billy Brown") 0 Then With Worksheets("Sheet1").Range("Data") vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With MsgBox vOurResult End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Find instead of VLOOKUP in search
Problem with your proposed solution:
XL97 does not digest: "MsgBox vOurResult.Row" it flags it as an error, and thus Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult does not work. obviously XL97, it does not track where a result came from. I can designate where to save "vOurResult" but I have yet to figure out how to retrive from the find command the row in which the search item was found. Michael Ernstoff Toppers wrote: Try: Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "BillyBrown") 0 Then With Worksheets("Sheet1").Range("Data") Set vOurResult = .Find(What:="BillyBrown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) End With MsgBox vOurResult.Row Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult End If End Sub "windsurferLA" wrote: Using XL97, Im seeking to search through a list to find those records that match a criteria, and then for each matching record, place the result in column 8 of that record. Ive been seeking to use the find function as it supposedly runs much faster than the VLOOKUP function. The code shown below performs the find function for one occurrence. When I learn how to make it work, Im confident I can include it in a loop to repeat the operation for multiple occurrences. : HOW DO I set the value of the cell in the eighth column of the matching record to the vOurResult???? If tried things like: Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult But the above line places the result on the line where the cursor happens to be instead of the line containing the matching record. (Code is plagiarized from: http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm) Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "Billy Brown") 0 Then With Worksheets("Sheet1").Range("Data") vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With MsgBox vOurResult End If End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using Find instead of VLOOKUP in search
My earlier update somehow did not get posted. .
I found that if I use Thomlinson's suggestion of the form: Set rngFound = rngToSearch.Find("This", , , xlPart) Where rngFound was defined as a range. Then: Worksheets("Sheet1").Cells(ActiveCell.Row, 10).Value = rngFound.Row would yield the row. Thus I concluded that XL97 would accept rngFound.Row So then I tried: rngFound = Range("Data").Find(What:="Mary Smith", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) where rngFound was defined as a range.. When it was followed by the following statement, I got zip Worksheets("Sheet1").Cells(ActiveCell.Row, 10).Value = rngFound.Row would yield the row. even though "Mary Smith" was within the Range("Data"). Any ideas what I'm doing wrong. windsurferLA wrote: Problem with your proposed solution: XL97 does not digest: "MsgBox vOurResult.Row" it flags it as an error, and thus Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult does not work. obviously XL97, it does not track where a result came from. I can designate where to save "vOurResult" but I have yet to figure out how to retrive from the find command the row in which the search item was found. Michael Ernstoff Toppers wrote: Try: Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "BillyBrown") 0 Then With Worksheets("Sheet1").Range("Data") Set vOurResult = .Find(What:="BillyBrown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) End With MsgBox vOurResult.Row Worksheets("Sheet1").Cells(vOurResult.Row, 8).Value = vOurResult End If End Sub "windsurferLA" wrote: Using XL97, Im seeking to search through a list to find those records that match a criteria, and then for each matching record, place the result in column 8 of that record. Ive been seeking to use the find function as it supposedly runs much faster than the VLOOKUP function. The code shown below performs the find function for one occurrence. When I learn how to make it work, Im confident I can include it in a loop to repeat the operation for multiple occurrences. : HOW DO I set the value of the cell in the eighth column of the matching record to the vOurResult???? If tried things like: Worksheets("Sheet1").Cells(ActiveCell.Row, 9).Value = vOurResult But the above line places the result on the line where the cursor happens to be instead of the line containing the matching record. (Code is plagiarized from: http://www.ozgrid.com/News/LookUpMatchIndexVBAFind.htm) Sub FindBillyBrown() Dim vOurResult ' FindBillyBrow Macro ' Using the Find Method over Vlookup ' If WorksheetFunction.CountIf(Worksheets("Sheet1").Ran ge("Data"), "Billy Brown") 0 Then With Worksheets("Sheet1").Range("Data") vOurResult = .Find(What:="Billy Brown", After:=.Cells(1, 1), _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3) End With MsgBox vOurResult End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF with Search/Find??? | Excel Worksheet Functions | |||
data filtering via vlookup or index/match/find or search withwildcard | Excel Worksheet Functions | |||
search a string withing a string : find / search hangs | Excel Programming | |||
Can Search find 2 or more "/"? | Excel Discussion (Misc queries) | |||
Search and find | Excel Programming |