On 16 Jun., 21:55, Gord Dibben wrote:
Anyhow, the code works just fine, although I had to comment out the
line 'If R = 1 Then R = R - 1. Leaving this line in the code results
in the overwriting of the existing pasted record. Commenting out this
line results in the code working absolutely perfectly, ie it appends
results of a new search to the results of earlier searches.

Not quite perfect. *With that line commented out try running the macro on a new
sheet with column A empty.

See that first search return is to A2

My attempt to correct that resulted in the overwrite.

I have revised code again to cover empty column without the overwriting.

Sub Find_it_and_copy_it()
'per jenssen jun 15, 2011
'revised by gord jun 16, 2011
* * Dim varWhat
* * Dim rngFound As Range
* * Dim R As Long
* * Dim fFound As Range

* * varWhat = Application.InputBox("Enter text to find")
* * Set rngFound = Sheets("list").Cells.Find(what:=varWhat, _
* * * * * * * LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
* * Set fFound = rngFound
* * R = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
* * If R = 1 And Range("A" & R).Value = "" Then
* * * * R = R - 1
* * End If
* * If Not rngFound Is Nothing Then
* * * * Do
* * * * * * R = R + 1
* * * * * * rngFound.EntireRow.Copy Cells(R, 1)
* * * * * * Set rngFound = Sheets("list").Cells.FindNext(after:=rngFound)
* * * * Loop Until rngFound.Address = fFound.Address
* * End If
End Sub

Gord



Anyhow, the code works just fine, although I had to comment out the
line 'If R = 1 Then R = R - 1. Leaving this line in the code results
in the overwriting of the existing pasted record. Commenting out this
line results in the code working absolutely perfectly, ie it appends
results of a new search to the results of earlier searches.- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Gord,

great. Now it is really perfect. Works like a charm.
Thank you very much for your great and swift support. This is
professionalism.

Regards, Andreas