Hi,
can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row and
then copy the "nn" row to the new row. I have tried a lot of different
versions for myself in VBA but can not seem to get it to work and don't
understand how to set it up to work.

thx,
Stelllar

try this sub and check if it is ok
Option Explicit


Public Sub test()
Dim f
With ActiveSheet.Columns("d:d")
Set f = (.Find("nn"))
If Not f Is Nothing Then GoTo line1
GoTo line2
End With
line1:
f.Activate
Dim values As Range
Set values = Range(ActiveCell.End(xlToLeft), ActiveCell.End(xlToRight))
ActiveCell.Offset(1, 0).EntireRow.Insert
values.Copy Destination:=ActiveCell.End(xlToLeft).Offset(1, 0)
line2:
End Sub
=============================================



stelllar wrote in message
oups.com...
Hi,
can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row and
then copy the "nn" row to the new row. I have tried a lot of different
versions for myself in VBA but can not seem to get it to work and don't
understand how to set it up to work.

thx,
Stelllar

Hi,
thanks a lot, worked like a charm :-)
The whole line1/line2 thing was a really interesting treat. Have to
remember that one.



R.VENKATARAMAN wrote:
try this sub and check if it is ok
Option Explicit


Public Sub test()
Dim f
With ActiveSheet.Columns("d:d")
Set f = (.Find("nn"))
If Not f Is Nothing Then GoTo line1
GoTo line2
End With
line1:
f.Activate
Dim values As Range
Set values = Range(ActiveCell.End(xlToLeft),
ActiveCell.End(xlToRight))
ActiveCell.Offset(1, 0).EntireRow.Insert
values.Copy Destination:=ActiveCell.End(xlToLeft).Offset(1, 0)
line2:
End Sub
=============================================



stelllar wrote in message
oups.com...
Hi,
can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row
and
then copy the "nn" row to the new row. I have tried a lot of
different
versions for myself in VBA but can not seem to get it to work and
don't
understand how to set it up to work.

thx,
Stelllar

You would be best advised not to use line 1/ line 2 things. It is much more
straightforward to use a block if statement. Form your own opinion. Which
is easier to understand?

Option Explicit


Public Sub test()
Dim f As Range

With ActiveSheet.Columns("d:d")
Set f = (.Find("nn"))
End With

If Not f Is Nothing Then
f.EntireRow.Copy
f.Offset(1, 0).EntireRow.Insert
End If

Application.CutCopyMode = False

End Sub



--
Regards,
Tom Ogilvy

"stelllar" wrote in message
oups.com...
Hi,
thanks a lot, worked like a charm :-)
The whole line1/line2 thing was a really interesting treat. Have to
remember that one.



R.VENKATARAMAN wrote:
try this sub and check if it is ok
Option Explicit


Public Sub test()
Dim f
With ActiveSheet.Columns("d:d")
Set f = (.Find("nn"))
If Not f Is Nothing Then GoTo line1
GoTo line2
End With
line1:
f.Activate
Dim values As Range
Set values = Range(ActiveCell.End(xlToLeft),
ActiveCell.End(xlToRight))
ActiveCell.Offset(1, 0).EntireRow.Insert
values.Copy Destination:=ActiveCell.End(xlToLeft).Offset(1, 0)
line2:
End Sub
=============================================



stelllar wrote in message
oups.com...
Hi,
can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row
and
then copy the "nn" row to the new row. I have tried a lot of
different
versions for myself in VBA but can not seem to get it to work and
don't
understand how to set it up to work.

thx,
Stelllar

thank you Mr;. ogilvy. your advice is very valuable to me. shall follow in
future


Tom Ogilvy wrote in message
...
You would be best advised not to use line 1/ line 2 things. It is much
more
straightforward to use a block if statement. Form your own opinion.
Which
is easier to understand?

Option Explicit


Public Sub test()
Dim f As Range

With ActiveSheet.Columns("d:d")
Set f = (.Find("nn"))
End With

If Not f Is Nothing Then
f.EntireRow.Copy
f.Offset(1, 0).EntireRow.Insert
End If

Application.CutCopyMode = False

End Sub



--
Regards,
Tom Ogilvy

"stelllar" wrote in message
oups.com...
Hi,
thanks a lot, worked like a charm :-)
The whole line1/line2 thing was a really interesting treat. Have to
remember that one.



R.VENKATARAMAN wrote:
try this sub and check if it is ok
Option Explicit


Public Sub test()
Dim f
With ActiveSheet.Columns("d:d")
Set f = (.Find("nn"))
If Not f Is Nothing Then GoTo line1
GoTo line2
End With
line1:
f.Activate
Dim values As Range
Set values = Range(ActiveCell.End(xlToLeft),
ActiveCell.End(xlToRight))
ActiveCell.Offset(1, 0).EntireRow.Insert
values.Copy Destination:=ActiveCell.End(xlToLeft).Offset(1, 0)
line2:
End Sub
=============================================



stelllar wrote in message
oups.com...
Hi,
can you please help me out with this one:
I have a sheet where I want to find the value "nn" in column D.
Wherever d contains "nn" I want to insert a new row below this row
and
then copy the "nn" row to the new row. I have tried a lot of
different
versions for myself in VBA but can not seem to get it to work and
don't
understand how to set it up to work.

thx,
Stelllar