Solved

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

Posted on 2012-03-26
6
219 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:dallagmm
  • 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:dallagmm
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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:dallagmm
ID: 37858083
Excellent
0
 
LVL 15

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now