Return Exact Value

[Dean]

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 = rCopyTffset(1, 0)
End If
End With
Next rPointer
Application.ScreenUpdating = True
MsgBox "Search Completed"
End Sub

 

[Tom Ogilvy]

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.

 

[paul.robinson]

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

 

[Dean]

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....

 

[Tom Ogilvy]

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.

 

[paul.robinson]

Can't seem to stop being sloppy when it comes to testing things before
responding!
Paul

 

[Tom Ogilvy]

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