Help with grep-like function

[sabot7726]

Hello all,

I resurrected an old function someone here helped me build a few years ago but doesn't seem to work in Office 2007. Basically I have a button in a Search tab with this code attached, then I simply enter text into a field (J1) and hit the button.

It will then search a different tab (Issues) in the sheet, then come back with only the rows that match my search criteria, in effect grepping the Search tab to only show stuff from the Issues tab which matches the search term.

It works in 2007 compatibility mode but does not work in 2007 normal mode. Specifically, in 2007 compatibility mode in the old spreadsheet it works fine, and can display an unlimited number of rows. In 2007 normal mode, in the new spreadsheet, it only ever shows 1 row. I need it to show unlimited rows like it used to.

Does anyone know what I should change in the sub so I don't have to tell everyone to use it in compatibility mode? Thanks!

Here's the sub code:

Sub test()

Application.ScreenUpdating = False
ActiveSheet.Range("a2:t1000").Clear
With Sheets("Issues").UsedRange '<----- change to sheet name that contains the data
    Set c = .Find(Range("J1").Value, LookIn:=xlValues) '<----- "*a*" can be replaced with a range reference (this is search value)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            c.EntireRow.Copy Sheets("Search").Range("A" & Rows.Count).End(xlUp).Offset(1) '<-----change to sheet name that you want to return the data to
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With

End Sub

 

[shg]

Try this:

Sub test()
    Dim wks         As Worksheet
    Dim rFind       As Range
    Dim sAddr       As String

    Set wks = Worksheets("Search")
    wks.Range("a2:t1000").Clear

    
    With Worksheets("Issues").UsedRange
        Set rFind = .Find(What:=Range("J1").Value, LookIn:=xlValues)
        

        If Not rFind Is Nothing Then
            sAddr = rFind.Address

            
            Do
                rFind.EntireRow.Copy wks.Cells(wks.Rows.Count, "A").End(xlUp)(2)
                Set rFind = .FindNext(rFind)
            Loop While rFind.Address <> sAddr
        End If
    End With
End Sub

 

[sabot7726]

That works to search, and I can see it finding more than one thing, but it's still only displaying 1 row instead of moving to the next row while the loop is active. Is there a way to tell it to go to row = row + 1?

 

[shg]

Sounds like there's nothing in the first cell of the found row, so it overwrites -- correct?

 

[sabot7726]

We got it working today, it was a problem with A1 being blank, thanks again for your help!

 

[shg]

You're welcome, good luck.