Correct Search String syntax for exact integer match, not all matches

[Batterychook]

New to VBA, so have searched and borrowed code and ideas from anywhere without really understanding yet what's going on.

What is the correct String search syntax to find an exact integer match in a column rather than returning all numbers containing the integer ?

That is, if I search for "3", the search returns 3, but also 13, 23, 30, 33, etc. But, I only want the exact match !

The code below extracts passenger details identified by a unique Pax # entered into an Input Box to enable printing a boarding pass. Unfortunately, it currently returns more than one passenger's details.

Sub Procedure1()
Dim strsearch As String, lastline As Integer, tocopy As Integer

strsearch = CStr(InputBox("enter the Pax # to Print Boarding Pass"))
lastline = Range("C100").End(xlUp).Row
j = 1
For i = 1 To lastline
For Each c In Range("C" & i)
If InStr(c.Text, strsearch) Then
tocopy = 1
End If
Next c
If tocopy = 1 Then
Rows(i).Copy Destination:=Sheets("Boarding Pass").Rows(j)
j = j + 1
End If
tocopy = 0
Next i
End Sub

This post supercedes a separate but similar problem posted last week

Thanks to all responders from a Newbie

BC

 

[Peter_SSs]

This post supercedes a separate but similar problem posted last week

I haven't seen that thread but it sounds like you really should have continued in that thread. Refer to #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines. Unless you have already done so, in this instance, you should at least reply to that thread pointing to this one.

If I have understood correctly this should not only solve the problem you mentioned but should also copy all the rows at once (if there are multiple rows).

I presume you have something somewhere to remove any existing values from the Boarding Pass sheet before copying the new values?

I have also assumed a heading row on the original data sheet.

Sub CopyPAXrows()
  Dim strsearch As String
  
  strsearch = InputBox("enter the Pax # to Print Boarding Pass")
  If Len(strsearch) > 0 Then
    Application.ScreenUpdating = False
    With Range("C1", Range("C" & Rows.Count).End(xlUp)).EntireRow
      .AutoFilter Field:=3, Criteria1:=strsearch
      If .Columns(3).SpecialCells(xlVisible).Cells.Count > 1 Then
        .Offset(1).Resize(.Rows.Count - 1).Copy Destination:=Sheets("Boarding Pass").Range("A1")
      Else
        MsgBox "Pax # " & strsearch & " not found"
      End If
      .AutoFilter
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[Batterychook]

Peter,
Thankyou for responding so quickly and providing a solution- much appreciated !!
Thankyou for also prompting me to provide a link to my previous question - although I had made some progress and hit another snag, I can see how the background info would have been useful to forum contributors - my apologies.

I've yet to test your solution but will respond when I have.

Thanks again,
BC


https://www.mrexcel.com/forum/excel-questions/1064547-copy-paste-multiple-cell-addresses-based-user-defined-numerical-value.html

 

[Peter_SSs]

Thankyou for also prompting me to provide a link to my previous question

Actually, I was prompting you to put a link to this question in your old thread.

.. you should at least reply to that thread pointing to this one.

So that anybody reading that old thread knows about this one and doesn't waste time in the old one possibly repeating what may have been done here - the whole reason we don't like duplicate threads.

 

[Batterychook]

I went back to my old thread to paste a link to this one assuggested, but note it is now closed. The problem raised in the old thread was regarding copy/pasting mergedcells – which ended up being too difficult, so I removed the merge in thespreadsheet.
This thread raises a different problem encountered as partof the same action on the same spreadsheet.
To add some context, I’m attempting to use an Input Box tosearch Column “C” of a sheet for a specific number. (This specific number identifies a passengernumber (Pax #) in a passenger manifest. Detailsrelating to the passenger identified by the Pax # in Column “C” are recorded inthe corresponding row. The intention isto then copy/paste the information from the row relating to the specific passengerof interest, into a separate sheet to be later printed onto a boarding pass.
At present, when a number (Pax #) is entered into the InputBox, the resulting search of Column “C” returns all instances of (Pax #) numberscontaining the Input Box numeral . Thatis, a search for Pax # “1” in Column “C” is also returning non-relevant Pax # “11”,Pax # “10”, Pax # “21”, etc.
Peter SSs has kindly offered a potential solution, but sofar I’m only getting a “Pax # not found” return from the Input Box initiatedsearch using his code, and I don’t know why. (the targeted Column “C” does contain the numbersearched for by the Input Box)
I do appreciate the support of this forum particularly dueto my lack of experience with VBA.
BC

 

[Peter_SSs]

The sheet that we are looking at column C must be the active sheet when the code is run. Is that the case?

Does column C contain numbers only?
eg
11
10
21

.. or do the cells contain consistent other text as well?
eg
Pax # 11
Pax # 10
Pax # 21

.. or might the cells be quite varied?
eg
Pax # 11
10
Passenger number 21

.. or something else?
Realistic varied samples?

 

[Batterychook]

Peter,
Thanks from a newbie for persisting with this.

Yes, the sheet is the active sheet.

Column "C" has a header "Pax #" at row 10. Other than the header, the column contains numbers only. Snapshot below.

Thanks !!
BC

Book1
	C	D	E	F	G	H	I	J	K	L
8
9		Passenger Manifest
10	Pax #		Pax Name	Group	Section	Aircraft	Pilot	Flight #	Depart time	WT
11	1		John Doe	Central	junior	ABC	Harry	1	9:20	56
12	2		Bob Doe	West	senior	ABC	Harry			64
13	3		Steve Did	East	adult	ABC	Harry			52
14	4		Jane Zee	Central	junior	DEF	George	2	10:20	70
15	5		Chris Jay	Central	junior	DEF	George			48
16	6		Evan Blog	East	adult	DEF	George			92
17	7		Jeff Bell	South	junior	XYZ	Alfred	7	11:20	58
18	8		Zen Bee	Mid West	senior	XYZ	Alfred			54
19	9		Brock Jones	North	teen	XYZ	Alfred			62
Sheet1

 

[Peter_SSs]

Thanks for the sample data & layout. Because the headers are in row 10, not row 1 as I had assumed, the code requires just one change

With Range("C10", Range("C" & Rows.Count).End(xlUp)).EntireRow

I copied your sample data to my Sheet1 in exactly those cells shown, changed cell C15 from 5 to 53 (so that I had more than one row with a 3 in the Pax #), ran the code and entered 3 in the input box.
On the 'Boarding Pass' sheet I got the row of data for Steve Did, as expected.

 

[Batterychook]

Peter,

Success !! Thankyou very much for your support !

You have helped me greatly and by doing so have helped a children's charity - so thankyou from them too !

Regards,
BC

 

[Peter_SSs]

Peter,

Success !! Thankyou very much for your support !

You have helped me greatly and by doing so have helped a children's charity - so thankyou from them too !

Regards,
BC

You're welcome. Glad you got it to work in the end.