Prev: can quadratic equations be solved in excel
Next: Excel formula
From: Jeremy on 19 Apr 2010 09:19
How would I write a scrtip to insert a row under the number in A that holds a
"NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in
it and paste it in the row that was inserted underneith. When it copies the
row it will copy the whole row except what is in A.

Example Before
A B
1 MT 12
2 D 14
3 x 15


Example After
1 2
1 MT 12
2 12
3 D 14
4 X 15


Thanks

From: Luke M on 19 Apr 2010 09:30
Sub CopyInsert()
'Find the last cell in column A
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = LastRow To 1 Step -1
If Cells(i, 1).Value = "NS" Or Cells(i, 1).Value = "MT" Then
'Copy row
Cells(i, 1).EntireRow.Copy
'Insert new row
Cells(i + 1, 1).Insert Shift:=xlDown
'Clear the inserted row's A cell
Cells(i + 1, 1).ClearContents
End If
Next

End Sub

--
Best Regards,

Luke M
"Jeremy" <Jeremy(a)discussions.microsoft.com> wrote in message
news:7BC6E044-30E9-413A-86F2-F88BF4A3B8A2(a)microsoft.com...
> How would I write a scrtip to insert a row under the number in A that
> holds a
> "NS" or "MT"? I would also like it to copy the row that has "NS" or "MT"
> in
> it and paste it in the row that was inserted underneith. When it copies
> the
> row it will copy the whole row except what is in A.
>
> Example Before
> A B
> 1 MT 12
> 2 D 14
> 3 x 15
>
>
> Example After
> 1 2
> 1 MT 12
> 2 12
> 3 D 14
> 4 X 15
>
>
> Thanks
>


From: Mike H on 19 Apr 2010 09:39
Jeremy

try this

Sub Insert_Copy()
Set Sht = Sheets("Sheet1") 'Change to suit
Dim x As Long
MyColumn = "A"
For x = Sht.Cells(Rows.Count, MyColumn).End(xlUp).Row To 1 Step -1
If UCase(Sht.Cells(x, MyColumn)) = "MT" _
Or UCase(Sht.Cells(x, MyColumn)) = "MS" Then
Sht.Rows(x + 1).insert
Sht.Rows(x).Copy Destination:=Cells(x + 1, 1)
Sht.Cells(x + 1, 1).ClearContents
End If
Next x
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jeremy" wrote:

> How would I write a scrtip to insert a row under the number in A that holds a
> "NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in
> it and paste it in the row that was inserted underneith. When it copies the
> row it will copy the whole row except what is in A.
>
> Example Before
> A B
> 1 MT 12
> 2 D 14
> 3 x 15
>
>
> Example After
> 1 2
> 1 MT 12
> 2 12
> 3 D 14
> 4 X 15
>
>
> Thanks
>
 | 
Pages: 1
Prev: can quadratic equations be solved in excel
Next: Excel formula