Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 20th 06, 08:17 AM posted to microsoft.public.excel.misc
Joy Joy is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 29
Default Insert rows (1 to 4) if the cell value equals a fixed word

I am looking for a macro to run once the fixed word in cell A13 for example
equals to “Health” and after the macro found the that cell will insert 1 to 3
rows and writing the following;
Cell A14 = “Health group A” --- This should be first row to be inserted
Cell A15 = “Health group B” --- This should be 2nd row to be inserted
Cell A16 = “Health group C” --- This should be 3rd row to inserted.
And than the macro should also read the information in Cell B13 and copy it
in B14 where the first row has been inserted and so on till the last row.

A B C D E
13 Health 110 1101 11011 100
14 Health group A 110 1101 11011 100
15 Health group B 110 1101 11011 100
16 Health group C 110 1101 11011 100


Any help will be very much appreciated


  #2   Report Post  
Old July 20th 06, 08:40 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 179
Default Insert rows (1 to 4) if the cell value equals a fixed word

Hi
Try this one

Sub Joy()

With ActiveSheet

For Each cell In .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
If Trim(ActiveCell.Value) = "" Then Exit Sub
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-2, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.Value = ActiveCell.Value & " Group A"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = ActiveCell.Value & " Group B"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = ActiveCell.Value & " Group C"
ActiveCell.Offset(1, 0).Range("A1").Select

Next
End With
End Sub


"Joy" wrote:

I am looking for a macro to run once the fixed word in cell A13 for example
equals to “Health” and after the macro found the that cell will insert 1 to 3
rows and writing the following;
Cell A14 = “Health group A” --- This should be first row to be inserted
Cell A15 = “Health group B” --- This should be 2nd row to be inserted
Cell A16 = “Health group C” --- This should be 3rd row to inserted.
And than the macro should also read the information in Cell B13 and copy it
in B14 where the first row has been inserted and so on till the last row.

A B C D E
13 Health 110 1101 11011 100
14 Health group A 110 1101 11011 100
15 Health group B 110 1101 11011 100
16 Health group C 110 1101 11011 100


Any help will be very much appreciated



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I insert the address of a selected cell into a fixed cell cox Excel Discussion (Misc queries) 2 May 27th 06 07:44 PM
Help with this conditional IF statement C-Dawg Excel Discussion (Misc queries) 3 May 15th 06 06:01 PM
Copy Word table into Excel cell by cell hg Excel Discussion (Misc queries) 3 December 15th 04 05:43 PM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM
parse cell and insert rows? Gorrila Grod Excel Worksheet Functions 3 November 5th 04 09:19 AM


All times are GMT +1. The time now is 09:43 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017