Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
I have listed the code below which allows me to search column "A" for a
value and return those results on another sheet. I have 5500 rows of data which have numbers in column "A" (lets call them department codes) from 1 to 30. The problem I am having as an example is when I search for department code "7" the code below returns department "7", "17" and "27" I would really appreciate some guidance on how to complete the search but only return results for the numerical value I am searching for. It's a little beyond my skills.... I think this line is the problem: If InStr(1, .Value, MyCriteria) 0 Then Thanks, Dean Sub Macro2() Dim LastRow As Long, MyCriteria, _ rCriteriaField As Range, rPointer As Range, rCopyTo As Range ' This variable has the value of the criteria by which you intend ' to select records to extract. Lets assume you are evaluating ' the entries in column A of your source table. This can be either ' text or numeric. Application.ScreenUpdating = False MyCriteria = InputBox("Enter Dept Code") If MyCriteria = "" Then Exit Sub ' Initialize a variable for the last possible record in a worksheet If Left(Application.Version, 1) < 8 Then _ LastRow = 5570 Else LastRow = 65536 With ThisWorkbook ' Initialize a range object variable for the entire populated ' area of column B (excluding row 1 for a header) With Worksheets("database") Set rCriteriaField = .Range(.Cells(1, 1), _ .Cells(Application.Max(1, _ .Cells(LastRow, 1).End(xlUp).Row), 1)) End With ' Initialize a range object variable to serve as a pointer ' for the records in sheet 2 Set rCopyTo = .Worksheets("found").Cells(1, 1) End With ' Loop through all the records in your source data table For Each rPointer In rCriteriaField With rPointer ' If there is a match on the criteria in col A then copy ' the record to the destination table If InStr(1, .Value, MyCriteria) 0 Then .Resize(, 5).Copy rCopyTo.PasteSpecial xlPasteValues ' Advance the pointer in your destination table to the ' next available row Set rCopyTo = rCopyTo.Offset(1, 0) End If End With Next rPointer Application.ScreenUpdating = True MsgBox "Search Completed" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
if you have numbers in column A, why use Instr. Why not use equality:
If .Value = MyCriteria then rather than If InStr(1, .Value, MyCriteria) 0 Then If the department code is embedded in a larger number string, then does it fall at a particular position? More would need to be known about what the values in a look like and how they are structured. -- Regards, Tom Ogilvy "Dean" wrote in message ups.com... I have listed the code below which allows me to search column "A" for a value and return those results on another sheet. I have 5500 rows of data which have numbers in column "A" (lets call them department codes) from 1 to 30. The problem I am having as an example is when I search for department code "7" the code below returns department "7", "17" and "27" I would really appreciate some guidance on how to complete the search but only return results for the numerical value I am searching for. It's a little beyond my skills.... I think this line is the problem: If InStr(1, .Value, MyCriteria) 0 Then Thanks, Dean Sub Macro2() Dim LastRow As Long, MyCriteria, _ rCriteriaField As Range, rPointer As Range, rCopyTo As Range ' This variable has the value of the criteria by which you intend ' to select records to extract. Lets assume you are evaluating ' the entries in column A of your source table. This can be either ' text or numeric. Application.ScreenUpdating = False MyCriteria = InputBox("Enter Dept Code") If MyCriteria = "" Then Exit Sub ' Initialize a variable for the last possible record in a worksheet If Left(Application.Version, 1) < 8 Then _ LastRow = 5570 Else LastRow = 65536 With ThisWorkbook ' Initialize a range object variable for the entire populated ' area of column B (excluding row 1 for a header) With Worksheets("database") Set rCriteriaField = .Range(.Cells(1, 1), _ .Cells(Application.Max(1, _ .Cells(LastRow, 1).End(xlUp).Row), 1)) End With ' Initialize a range object variable to serve as a pointer ' for the records in sheet 2 Set rCopyTo = .Worksheets("found").Cells(1, 1) End With ' Loop through all the records in your source data table For Each rPointer In rCriteriaField With rPointer ' If there is a match on the criteria in col A then copy ' the record to the destination table If InStr(1, .Value, MyCriteria) 0 Then .Resize(, 5).Copy rCopyTo.PasteSpecial xlPasteValues ' Advance the pointer in your destination table to the ' next available row Set rCopyTo = rCopyTo.Offset(1, 0) End If End With Next rPointer Application.ScreenUpdating = True MsgBox "Search Completed" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
Hi
Presumably you have used InStr because the values you are looking for combine text and numbers, otherwise you would simply test "If .Value = 7 then" Hard to say what to do without seeing what your values look like. you could try the Val function e.g Val("Record7") = 7 Val("Record27") = 27 Val("7bRecord") = 7 Val("first7Record") = 7 Val("Record7and3") = 7 The Val kicks in when it meets a numerical character and stops when it meets a nonnumeric character. The period . is a decimal separator so is counted. regards Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
Thanks Tom for the reply.
I tried the suggested change but the code will not return any search results. The department numbers in column A are randomly scattered EG: column A 1 5 9 17 13 9 8 15 etc etc.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
Think you need to reread the help file on VAL
from the immediate window: ? Val("first7Record") 0 Val stops at the first nonnumeric character. It doesn't kick in at the first numberic character. from help nn VAL: The Val function stops reading the string at the first character it can't recognize as part of a number. -- Regards, Tom Ogilvy wrote in message oups.com... Hi Presumably you have used InStr because the values you are looking for combine text and numbers, otherwise you would simply test "If .Value = 7 then" Hard to say what to do without seeing what your values look like. you could try the Val function e.g Val("Record7") = 7 Val("Record27") = 27 Val("7bRecord") = 7 Val("first7Record") = 7 Val("Record7and3") = 7 The Val kicks in when it meets a numerical character and stops when it meets a nonnumeric character. The period . is a decimal separator so is counted. regards Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
Can't seem to stop being sloppy when it comes to testing things before
responding! Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Exact Value
It sounds like you have a data problem. there may be spaces or other
invisible characters in the cell with your numbers. Using Instr will avoid that problem, but causes the problem you originally posted about. If any stray characters are only the right side of your number, you can try If InStr(1, Trim(.Value), Trim(MyCriteria),vbTextCompare) = 1 Then rather than If InStr(1, .Value, MyCriteria) 0 Then -- Regards, Tom Ogilvy "Dean" wrote in message oups.com... Thanks Tom for the reply. I tried the suggested change but the code will not return any search results. The department numbers in column A are randomly scattered EG: column A 1 5 9 17 13 9 8 15 etc etc.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to return exact match | Excel Worksheet Functions | |||
Vlookup - return exact phrase | Excel Discussion (Misc queries) | |||
Need a function to return EXACT row number of a match | Excel Worksheet Functions | |||
Font color of exact function return in excel should be customize | Excel Programming | |||
Macro to compare values and return exact matching value | Excel Programming |