Find a value, then insert a row below that row and copy the contents to the new row
 

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

Find a value, then insert a row below that row and copy the contents to the new row
 

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

Find a value, then insert a row below that row and copy the contents to the new row
 

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

Find a value, then insert a row below that row and copy the contents to the new row
 

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

Find a value, then insert a row below that row and copy the contents to the new row
 

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