Solved

how to insert a new row in the excel sheet based on a condtion

Posted on 2012-03-26
6
222 Views
Last Modified: 2012-04-22
how to insert a new row in the excel sheet based on a condtion
For example I need to insert the following line every time there is m in column 1:

*/ ANNONAME:"YT_West_F683.flt"

A copy of my excel sheet is attached

                             
m      748791.3125      2742759.5      1      0      YT_West_F683.flt
d      748675.625      2742808      1      0      YT_West_F683.flt
d      748552.375      2742842      1      0      YT_West_F683.flt
m      741813.3125      2755022      1      0      YT_West_F680.flt
d      741707.25      2755088.75      1      0      YT_West_F680.flt
d      741618.3125      2755188      1      0      YT_West_F680.flt
d      741500.75      2755233.5      1      0      YT_West_F680.flt
d      741397.125      2755305      1      0      YT_West_F680.flt
d      741303      2755394.5      1      0      YT_West_F680.flt
d      741194.125      2755456      1      0      YT_West_F680.flt
d      741087.125      2755521      1      0      YT_West_F680.flt
d      740980.25      2755586.5      1      0      YT_West_F680.flt
m      754808      2751540.5      1      0      YT_West_F548.flt
d      754703      2751609.5      1      0      YT_West_F548.flt
d      754599.25      2751680.75      1      0      YT_West_F548.flt
d      754504.25      2751768.75      1      0      YT_West_F548.flt
d      754397.875      2751835      1      0      YT_West_F548.flt
d      754289.1875      2751897      1      0      YT_West_F548.flt
d      754189.0625      2751975      1      0      YT_West_F548.flt
d      754087.625      2752051      1      0      YT_West_F548.flt
d      753983.25      2752121      1      0      YT_West_F548.flt
d      753882.625      2752198.25      1      0      YT_West_F548.flt
d      753780.125      2752272      1      0      YT_West_F548.flt
d      753677.125      2752345      1      0      YT_West_F548.flt
d      753573.625      2752416.5      1      0      YT_West_F548.flt
d      753472.5      2752493      1      0      YT_West_F548.flt
d      753375.875      2752577.5      1      0      YT_West_F548.flt
d      753268.875      2752642.75      1      0      YT_West_F548.flt
d      753139.6875      2752665.75      1      0      YT_West_F548.flt
m      756466.875      2741516      1      0      YT_West_F547.flt
d      756335.375      2741534.5      1      0      YT_West_F547.flt
d      756246.75      2741634.5      1      0      YT_West_F547.flt
d      756132.1875      2741685.25      1      0      YT_West_F547.flt
d      756035.6875      2741770.25      1      0      YT_West_F547.flt
d      755924.375      2741827.25      1      0      YT_West_F547.flt
d      755846.375      2741947.5      1      0      YT_West_F547.flt
m      748991.375      2753368      1      0      YT_West_F543.flt
d      748913.3125      2753488.5      1      0      YT_West_F543.flt
d      748810      2753560.5      1      0      YT_West_F543.flt
d      748721      2753660      1      0      YT_West_F543.flt
d      748598      2753694.5      1      0      YT_West_F543.flt
d      748494.8125      2753767      1      0      YT_West_F543.flt
d      748394.75      2753845      1      0      YT_West_F543.flt
test.xlsx
0
Comment
Question by:Mohammed Dallag
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37764644
you want to loop through the records set?

for each style?

then if col1 = m, insert a new row?  below the row with m in?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37764661
Sub InsertAnnote()
Dim strInsert As String
strInsert = Range("A1").Value
Range("A2").Activate
    While ActiveCell.Value <> ""
        If ActiveCell.Value = "m" Then
            ActiveCell.Offset(1, 0).Activate
            If ActiveCell.Value <> strInsert Then
                Selection.EntireRow.Insert
                ActiveCell.Value = strInsert
            End If
        End If
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

Open in new window

edit: added a check to see if m line already annotated..
test.xls
0
 

Author Comment

by:Mohammed Dallag
ID: 37764838
Thank you but as you notice in the file that the annotation is diferent for each m.
I need to read the annotation from the 6th column corsponding to the right m and have it in the following format  
*/ ANNONAME:"column 6"

Regards,

Dallag
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 15

Accepted Solution

by:
Simon Ball earned 500 total points
ID: 37768796
so the annotation needs to be a concatenation of */ ANNONAME:"<insert content of previous records column6 value>"?

Sub InsertAnnote()
Dim strDefault As String
strDefault = "*/ ANNONAME:" & Left("""", 1) & "<insert>" & Left("""", 1)
Dim strInsert As String
strInsert = ""
Range("A2").Activate
    While ActiveCell.Value <> ""
        If ActiveCell.Value = "m" Then
            ActiveCell.Offset(1, 0).Activate
            strInsert = Replace(strDefault, "<insert>", ActiveCell.Offset(-1, 5).Value, 1, 1)
            If ActiveCell.Value <> strInsert Then
                Selection.EntireRow.Insert
                ActiveCell.Value = strInsert
            End If
        End If
        ActiveCell.Offset(1, 0).Activate
    Wend
End Sub

Open in new window

test.xls
0
 

Author Closing Comment

by:Mohammed Dallag
ID: 37858083
Excellent
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37878431
my pleasure :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question