Dear Experts:

Below macro ...
(1)... creates a user-defined search dialog box and ...
(2)... searches for data records that are listed in a
'xlsheetveryhidden'-worksheet (Sheet name = list)... and
(3)... if found, the data record is copied into the active cell of the
current worksheet.

I would like to have this macro rewritten so that ...
.... Excel copies the first instance of a found value/data record right
into row 1 of the current worksheet. Subsequent hits should then be
copied into row 2, row 3 etc. and so forth no matter which cell is
activated.

Is this possible? Help is very much appreciated. Thank you very much
in advance.

Regards, Andreas


Sub Find_it_and_copy_it()
Dim varWhat
Dim rngfound As Range
varWhat = Application.InputBox("Enter text to find")
Set rngfound = Sheets("list").Cells.Find(what:=varWhat,
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not rngfound Is Nothing Then
rngfound.EntireRow.Copy Cells(ActiveCell.Row, 1)
End If
End Sub

On 14 Jun., 09:55, AndreasHermle
wrote:
Dear Experts:

Below macro ...
(1)... creates a user-defined search dialog box and ...
(2)... searches for data records that are listed in a
'xlsheetveryhidden'-worksheet (Sheet name = list)... and
(3)... if found, the data record is copied into the active cell of the
current worksheet.

I would like to have this macro rewritten so that ...
... Excel copies the first instance of a found value/data record right
into row 1 of the current worksheet. Subsequent hits should then be
copied into row 2, row 3 etc. and so forth no matter which cell is
activated.

Is this possible? Help is very much appreciated. Thank you very much
in advance.

Regards, Andreas

Sub Find_it_and_copy_it()
* *Dim varWhat
* *Dim rngfound As Range
* *varWhat = Application.InputBox("Enter text to find")
* *Set rngfound = Sheets("list").Cells.Find(what:=varWhat,
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
* *If Not rngfound Is Nothing Then
* * * rngfound.EntireRow.Copy Cells(ActiveCell.Row, 1)
* *End If
End Sub

This should do it:

Sub Find_it_and_copy_it()
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
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

On 15 Jun., 16:05, Per Jessen wrote:
On 14 Jun., 09:55, AndreasHermle
wrote:





Dear Experts:

Below macro ...
(1)... creates a user-defined search dialog box and ...
(2)... searches for data records that are listed in a
'xlsheetveryhidden'-worksheet (Sheet name = list)... and
(3)... if found, the data record is copied into the active cell of the
current worksheet.

I would like to have this macro rewritten so that ...
... Excel copies the first instance of a found value/data record right
into row 1 of the current worksheet. Subsequent hits should then be
copied into row 2, row 3 etc. and so forth no matter which cell is
activated.

Is this possible? Help is very much appreciated. Thank you very much
in advance.

Regards, Andreas

Sub Find_it_and_copy_it()
* *Dim varWhat
* *Dim rngfound As Range
* *varWhat = Application.InputBox("Enter text to find")
* *Set rngfound = Sheets("list").Cells.Find(what:=varWhat,
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
* *If Not rngfound Is Nothing Then
* * * rngfound.EntireRow.Copy Cells(ActiveCell.Row, 1)
* *End If
End Sub

This should do it:

Sub Find_it_and_copy_it()
* *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
* *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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Per,

thank you very much for your quick support. I am afraid to tell you
that the code keeps writing all the found values right into A1, i.e.
overwriting the values over and over.
Any idea why?

Regards, Andreas

On 15 Jun., 16:52, AndreasHermle
wrote:
On 15 Jun., 16:05, Per Jessen wrote:





On 14 Jun., 09:55, AndreasHermle
wrote:

Dear Experts:

Below macro ...
(1)... creates a user-defined search dialog box and ...
(2)... searches for data records that are listed in a
'xlsheetveryhidden'-worksheet (Sheet name = list)... and
(3)... if found, the data record is copied into the active cell of the
current worksheet.

I would like to have this macro rewritten so that ...
... Excel copies the first instance of a found value/data record right
into row 1 of the current worksheet. Subsequent hits should then be
copied into row 2, row 3 etc. and so forth no matter which cell is
activated.

Is this possible? Help is very much appreciated. Thank you very much
in advance.

Regards, Andreas

Sub Find_it_and_copy_it()
* *Dim varWhat
* *Dim rngfound As Range
* *varWhat = Application.InputBox("Enter text to find")
* *Set rngfound = Sheets("list").Cells.Find(what:=varWhat,
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
* *If Not rngfound Is Nothing Then
* * * rngfound.EntireRow.Copy Cells(ActiveCell.Row, 1)
* *End If
End Sub

This should do it:

Sub Find_it_and_copy_it()
* *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
* *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- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Per,

thank you very much for your quick support. I am afraid to tell you
that the code keeps writing all the found values right into A1, i.e.
overwriting the values over and over.
Any idea why?

Regards, Andreas- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Andreas,

It sounds very strange!

I just tested the code and it pasted each hit to a new row, always
starting from row 1 when I start a new search. Are you sure, that
there is more than one match in the 'List' sheet?

Regards, Per

I also tested Per's code and it worked as advertised.


Gord Dibben MS Excel MVP

On Wed, 15 Jun 2011 11:04:33 -0700 (PDT), Per Jessen
wrote:

Hi Andreas,

It sounds very strange!

I just tested the code and it pasted each hit to a new row, always
starting from row 1 when I start a new search. Are you sure, that
there is more than one match in the 'List' sheet?

Regards, Per

On 15 Jun., 21:06, Gord Dibben wrote:
I also tested Per's code and it worked as advertised.

Gord Dibben * * MS Excel MVP

On Wed, 15 Jun 2011 11:04:33 -0700 (PDT), Per Jessen
wrote:



Hi Andreas,

It sounds very strange!

I just tested the code and it pasted each hit to a new row, always
starting from row 1 when I start a new search. Are you sure, that
there is more than one match in the 'List' sheet?

Regards, Per- Zitierten Text ausblenden -

- Zitierten Text anzeigen -


Dear Gord and Per,

thank you very much for your support. This is really strange. These
are the sample values in the 'list'-sheet:
1754 Martin
1755 Gordon
1756 Jim
1757 Jones

On the other sheet of the same workbook I got a macro button linked to
your code.
Whenever I press it, I am prompted to enter text, I enter either 1754,
1755, 1756 or 1757. In case of entering 1754, the entire row, i.e.
1754 Martin is pasted into the very first row of the active
worksheet.
On running the macro again, I enter 1755 and the entire row, i.e. 1755
Gordon overwrites! the first entry, i.e. 1754 Martin and so forth.

Reading your code it makes absolute sense. I will try it on another
machine and then let you know.

Till then, Regards, Andreas