Find Records,

J

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
786
Office Version
  1. 365
Hi,


i have this huge spreasheet (attached) that i want to search for records and put the results temporary an new sheet but i am not good in vba, can any body help.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1110"]
<tbody>[TR]
[TD]CARRIER[/TD]
[TD]ACCOUNT NUMBER[/TD]
[TD]ACCOUNT NAME[/TD]
[TD]BTN[/TD]
[TD]WTN[/TD]
[TD]AUTH_CODE[/TD]
[TD]AUTH.CODE.STATUS[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000071411[/TD]
[TD]CENTURY 21 MOVING REAL ESTATE BC LTD[/TD]
[TD]2503784344[/TD]
[TD]0000000000[/TD]
[TD]2503784344[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000000021[/TD]
[TD]CN (IT TELECOM)[/TD]
[TD]2504597918[/TD]
[TD]0000000000[/TD]
[TD]2504597918[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000000021[/TD]
[TD]CN (IT TELECOM)[/TD]
[TD]2504597928[/TD]
[TD]0000000000[/TD]
[TD]2504597928[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000110709[/TD]
[TD]GLOBAL PARTNERS INSTITUTE[/TD]
[TD]6044484250[/TD]
[TD]0000000000[/TD]
[TD]6044484250[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000076963[/TD]
[TD]GEORGE RICHARD LOW[/TD]
[TD]2505773758[/TD]
[TD]0000000000[/TD]
[TD]2505773758[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000110024[/TD]
[TD]ROYAL LEPAGE LOCATIONS WEST REALTY[/TD]
[TD]2504937587[/TD]
[TD]0000000000[/TD]
[TD]2504937587[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000151077[/TD]
[TD]VILLAGE OF PORT ALICE[/TD]
[TD]2502843392[/TD]
[TD]0000000000[/TD]
[TD]2502843392[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507701317[/TD]
[TD]0000000000[/TD]
[TD]2507701317[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507708601[/TD]
[TD]0000000000[/TD]
[TD]2507708601[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507708602[/TD]
[TD]0000000000[/TD]
[TD]2507708602[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507709157[/TD]
[TD]0000000000[/TD]
[TD]2507709157[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507701317[/TD]
[TD]0000000000[/TD]
[TD]2507701317[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507708601[/TD]
[TD]0000000000[/TD]
[TD]2507708601[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507708602[/TD]
[TD]0000000000[/TD]
[TD]2507708602[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000105151[/TD]
[TD]ABC ALLEN BUSINESS COMMUNICATIONS[/TD]
[TD]2507709157[/TD]
[TD]0000000000[/TD]
[TD]2507709157[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000074693[/TD]
[TD]HOMELIFE GLENAYRE REALTY CO LTD[/TD]
[TD]6044627359[/TD]
[TD]0000000000[/TD]
[TD]6044627359[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000163771[/TD]
[TD]IKTEROS ENTERPRISES INC[/TD]
[TD]6044488790[/TD]
[TD]0000000000[/TD]
[TD]6044488790[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000164717[/TD]
[TD]BANCORP FINANCIAL SERVICES INC[/TD]
[TD]6046082717[/TD]
[TD]0000000000[/TD]
[TD]6046082717[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158736[/TD]
[TD]ESKIMO STEEL[/TD]
[TD]4034680910[/TD]
[TD]0000000000[/TD]
[TD]4034680910[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170542[/TD]
[TD]0000000000[/TD]
[TD]4034170542[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158736[/TD]
[TD]ESKIMO STEEL[/TD]
[TD]4034680909[/TD]
[TD]0000000000[/TD]
[TD]4034680909[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158736[/TD]
[TD]ESKIMO STEEL[/TD]
[TD]4034681736[/TD]
[TD]0000000000[/TD]
[TD]4034681736[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170513[/TD]
[TD]0000000000[/TD]
[TD]4034170513[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170514[/TD]
[TD]0000000000[/TD]
[TD]4034170514[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170515[/TD]
[TD]0000000000[/TD]
[TD]4034170515[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170516[/TD]
[TD]0000000000[/TD]
[TD]4034170516[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170517[/TD]
[TD]0000000000[/TD]
[TD]4034170517[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158775[/TD]
[TD]ASA ALLOYS INC EDMONTON[/TD]
[TD]4034170564[/TD]
[TD]0000000000[/TD]
[TD]4034170564[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]TELUS[/TD]
[TD]000158736[/TD]
[TD]ESKIMO STEEL[/TD]
[TD]4034639704[/TD]
[TD]0000000000[/TD]
[TD]4034639704[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000171397[/TD]
[TD]STERLING GROUP CORP[/TD]
[TD]6048501299[/TD]
[TD]0000000000[/TD]
[TD]6048501299[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000061435[/TD]
[TD]CONSULATE GENERAL OF JAPAN[/TD]
[TD]6046855895[/TD]
[TD]0000000000[/TD]
[TD]6046855895[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]BCTEL[/TD]
[TD]000095108[/TD]
[TD]TIDE RIP TOURS LTD.[/TD]
[TD]2509283092[/TD]
[TD]0000000000[/TD]
[TD]2509283092[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



thank you
 
Sort by date Sort by votes
The auth code is in table and somebody give a code I want to be able to list only that number with their related tisc_code from the list.

I tried to build a pivot table but I could not figured out.

Thank you
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Is this what you're after?
Code: 
Sub CopyFltr()

   Dim Auth As String
   
   Auth = InputBox("Please enter an AuthCode")
   If Len(Auth) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:J1").AutoFilter 8, Auth
      .Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy Sheets("[COLOR=#ff0000]Temp[/COLOR]").Range("A1")
      .Range("A1").AutoFilter
   End With

End Sub
Change the sheet name in red to suit
 
Upvote 0
Fluff said:
Is this what you're after?
Code: 
Sub CopyFltr()

   Dim Auth As String
   
   Auth = InputBox("Please enter an AuthCode")
   If Len(Auth) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:J1").AutoFilter 8, Auth
      .Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy Sheets("[COLOR=#ff0000]Temp[/COLOR]").Range("A1")
      .Range("A1").AutoFilter
   End With

End Sub
Change the sheet name in red to suit
Click to expand...

What he said ?
 
Upvote 0
thank you so much works, perfectly.

if is not too much to bother you, is there any to add if want to search for another cell not just auth_code and when transfer to "Temp" sheet can it give the records found count.

if can do it only, if not thanks for all your help.
 
Upvote 0
What would be the other column & search value?
Also, do you want to copy only those rows that match both values, or those rows that match either?
 
Upvote 0
Try this
Code: 
Sub CopyFltr()

   Dim Auth As String
   Dim Acc As String
   
   Auth = InputBox("Please enter an AuthCode")
   If Len(Auth) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If
   Acc = InputBox("Please enter an Account")
   If Len(Acc) = 0 Then
      MsgBox "Nothing entered"
      Exit Sub
   End If

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:J1").AutoFilter 8, Auth
      .Range("A1:J1").AutoFilter 4, Acc
      .Range("A1").CurrentRegion.SpecialCells(xlVisible).Copy Sheets("Temp").Range("A1")
      .Range("A1").AutoFilter
   End With

End Sub
 
Upvote 0
Sorry. Misposted. :)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top