Want to Display All Items Using Find Exact Match

[GWee]

I would like to find all the numbers that are the same and display them to review and put them in a separate sheet for print. Does anybody have the code for this? Using UserForm for this!

Thanks!

 

[erik.van.geit]

Hello, GWee,
please care of providing examples of what you're trying to do
as I read this, you are going to put for instance 50 times the same number in another sheet

find all the numbers that are the same and display them

so there is missing something to me or to your explanation

kind regards,
Erik

 

[GWee]

Ok I'll Try

Column A has 5 digit numbers some have the same number but different information throughout the row .

I need to pick up all the matching numbers in Column A to get the different info from those rows.

Thankyou for your help!

 

[erik.van.geit]

Option Explicit

Sub copy_rows()
Dim c As Range
Dim TextToFind As String
Dim firstAddress As String
TextToFind = TextBox1 'or whatever control on your userform
With Sheets(1).Columns(1)
    Set c = .Find(TextToFind, LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.EntireRow.Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

faster code: if a lot of rows to do

Option Explicit

Sub copyrows_if()
'Erik Van Geit
'040223
Dim rng As Range
Dim LC As Integer   'last column
Dim LR As Long      'last row
Dim TextToFind As String

Const HR As Long = 1    'header row (first data = HR + 1)
TextToFind = 123
With Sheets(1)
LR = .Cells(Rows.Count, 1).End(xlUp).Row
LC = .Cells.Find("*", .[A1], xlFormulas, xlPart, xlByColumns, xlPrevious, False, False).Column
Set rng = .Range(.Cells(2, LC + 1), .Cells(LR, LC + 1))
End With

    With rng
        .FormulaR1C1 = "=IF(RC1=" & TextToFind & ",1,0)"
        .Copy
        .PasteSpecial Paste:=xlValues
    End With
    
With Sheets(1)
.Range(.Cells(HR, 1), .Cells(LR, LC + 1)).AutoFilter Field:=LC + 1, Criteria1:="1"
.Range(.Cells(HR + 1, 1), .Cells(LR, LC)).Copy Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

Sheets(1).Range("A1").AutoFilter
rng.ClearContents
End Sub

 

[Peter_SSs]

GWee

Do you need to use code? And do you need to put the information on another sheet?

Could you just use Data|Filter|AutoFilter then filter column A for the number you want and print the results?

If you do want the information on another sheet, could you filter as described above, copy the resultant display and paste on the other sheet?

 

[erik.van.geit]

Hi, guys,
there is some "overkill" in my second method
(using autofilter)
this was updated from another file where the condition was more complicated, hence the extra column (which you will only see interupting the code), which isn't needed in fact here
nevermind, it is fast and easy to use

if you would like to get the update, just ask ...
(no time left by now)

kind regards,
Erik

 

[GWee]

Thanks Peter For Your Comment!! My reply....

GWee

Do you need to use code? And do you need to put the information on another sheet?

Could you just use Data|Filter|AutoFilter then filter column A for the number you want and print the results?

If you do want the information on another sheet, could you filter as described above, copy the resultant display and paste on the other sheet?

I'm using a userform to get the information and then edit the textboxes, checkboxes, option boxes. I click on ENTER. Then if I see a problem/errors I would like to Undo. I somewhat found a start...What do you think if I can use:

Application.OnUndo "The Last Macro", "MyUndoMacro"

How would I put a command button on a userform using part/whole of this type of code?

Thanks so much for the help!

 

[erik.van.geit]

Gwee,
finally I see what you were asking here
http://www.mrexcel.com/board2/viewtopic.php?p=867706&highlight=#867706
let's undo the entire operation (method mentioned already in the other thread)
to test used just clearcontents

Option Explicit

Public ThisSheet As Worksheet
Public ThisShName As String
Public TryOutSheet As Worksheet
Public Const BackupShName As String = "backupsheet"

Sub test()
'Erik Van Geit
'051122

Set ThisSheet = ActiveSheet
ThisShName = ThisSheet.Name

Application.ScreenUpdating = False

    With ThisSheet
    .Copy After:=Sheets(Sheets.Count)
        Application.DisplayAlerts = False
        .Name = BackupShName
        Application.DisplayAlerts = True
    .Visible = xlSheetVeryHidden
    End With

Set TryOutSheet = ActiveSheet
TryOutSheet.Name = ThisShName

Application.ScreenUpdating = True

yourmacro

End Sub

Sub undo_this()
    With Application
    .ScreenUpdating = False
        ThisSheet.Visible = True
        .DisplayAlerts = False
        TryOutSheet.Delete
        .DisplayAlerts = True
        ThisSheet.Name = ThisShName
    .ScreenUpdating = True
    End With
End Sub

Sub yourmacro()
Cells.ClearContents
End Sub

first run the test
then "undo_this"
in between you can do other operations which will all be "undone"
the code is "quick-made" not tested in detail, but seems to work

kind regards,
Erik