Form and Code to Find and Select a Range

Mark F

Well-known Member
Joined
Jun 7, 2002
Messages
500
Hi all

I have a worksheet with around 8000 rows and 5 columns

I want the user to be able to type a 13 digit number into a form or entry box of some sort.

I then need the entered number to be "found" on the worksheet if it is present, and then columns A -E in the row that the number appears to be
selected and "copied". The copied info would then be manually pasted into another sheet.

If the number is not present in the sheet I would like an error message to appear.

Any suggestions where to start

Mark
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
Mark,

Following assumptions made:
1. Your numbers and data on in Sheet1.
2. The data in Sheet1 starts in row 4, with headings in row 3.
3. The numbers are in column A (they do not have to be sorted).
4. Found items are to be copied to sheet3, starting in row 2 (headings in row 1).

The macro uses cell G1 as the input cell for your numbers. Simply input your number in cell G1, and then Enter. The row (columns A:E) will be copied to Sheet3 and cell G1 will be cleared for the next number. If no number is found, an error message will be returned. This is how I have set up my test data:
Find a serial number and copy row to new WS.xls
ABCDEFGH
1
2xxxxxxxx
3NumberData2Data3Data4Data5
41936140935294172483368
51051442260127299397332
61425768846403156411453
71687161648116231358231
81825868581205379449190
91066656755342342145481
10184731753122269396266
1117167952185197265314
121857161002107465365100
131389526819369439303157
141754217461205379449190
151381012033165212359157
1611305038781925460321
Sheet1


Put the macro in the sheet1 module.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myRange As Range
Dim ws As Worksheet
Dim res As Variant
Dim myVal As Long

Set ws = Sheets(1)

On Error GoTo xit

With ws
   Set myRange = .Range("A4", .Cells(.Rows.Count, "A").End(xlUp))
   myVal = Range("G1")
      
   If myVal = 0 Then Exit Sub
   res = Application.Match(myVal, myRange, 0)
      If IsError(res) Then
           MsgBox "Serial No. not found"
           Range("G1").ClearContents
      Else
        With myRange(res)
          .Resize(1, 5) _
          .Copy Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0)
        End With
      End If
   
Range("G1").ClearContents
End With
 
Set ws = Nothing
Set myRange = Nothing

Application.ScreenUpdating = True
xit:
Application.ScreenUpdating = True
Exit Sub
End Sub
HTH

Mike
 

Mark F

Well-known Member
Joined
Jun 7, 2002
Messages
500
Mike

:oops: I am so sorry!!! You are 100% right :oops:

Please don't think I am not grateful for the time you took to reply and help although I would understand if you did :(

I appreciate any help I receive here.

Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,912
Members
413,952
Latest member
JGer

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
Top