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
|